Insert Date in to SQL Server 2008 Table

Q&A
1

Hey folks,

I have a variable in a process. It is a date. It is used to select a date of birth.

This needs to save to a sql server database table, with outher variables, to a field which is a datetime type in the database table.

Now, the sql query, without the date field - works fine. All data goes in.
With the date field, the sql query fails.

Do I need to do something to the variable ‘date’ for it to work with the sql field ‘datetime’?

Many thanks,
Jim

2

Hi Jim,

I suspect you just need to format the date/time into the correct field format…

‘2013-10-10’ for example…

see also: here and here.

regards
Seán

3

Hi,

The initial date format captures in Bonita is “Sun Sep 01 00:00:00 SGT 2013”. In database, i stored it in a column with string data type. Next time i want to use this data, i called the data and hold it in string variable. Then i parse it using this script:

import java.util.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;

SimpleDateFormat formatter = new SimpleDateFormat(“EEEE MMM dd HH:mm:ss z yyyy”);
String dateInString = d1;//d1 is a (string) bonita variable which i fetch from database

try {

	   Date date = formatter.parse(dateInString);
	   date

   } catch (Exception e)
   {
   System.out.println("Error");
   }

This maybe a long way but it might be a last resort =)

4

Hi Sean,
This worked for me:

def dateString = dateOfBirth.format(“YYYY-MM-dd”)
return dateString

Once done, I can use the var in my sql query and it saves.

Do you know of a better way? Perhaps a date variable that is automatically the above format?

Thanks,
Jim

5

Do you know of a better way?

The best I can get is

return dateOfBirth.format(“YYYY-MM-dd”)

I think that’s the best you’ll get.

6

Would you do this on submit of a form?

So the output to a variable would be field_birthDate.format(“YYYY-MM-dd”)

I am currently using a step just to convert the data called ‘convert date’ which runs a groovy connector to do the above.

Doing directly from a form would be much better, what do you think?