Workflow and ServiceDesk Community

Issue:  I'm trying to query a SQL table for a range of dates.  I've created the query and can run it just fine.   The format in the table is YYYY-MM-DD HH:MM:SS. 

I've tried to use date picker to get the dates but it comes out as  M/D/YYYY HH:MM:SS

I tried converting it with the "Convert to string" component but that just seems to ignore it and sends it out the same way it came in..

So then I just tried using drops down list components to create the date but then the SQL component squaks about it not being a DATE. 

This seems like it should be easy..  

Changing the SQL table isn't a possibliity since its already populated with over a thousand records.. 

Just guessing here... you might need to change your Date and Time formats under your Region and Language settings of your machine, if you want the format to be something different than M-D-YYYY.

Hi,

If I understood well your problem, on one side, you have a SQL query which needs as input a variable typed as DATETIME with format like YYYY-MM-DD HH:mm:ss; on the other side you have a DATEPICKER component which gives you a variable with a DATETIME formated like M/D/YYYY HH:mm:ss.

So, have tried to use the Split Text into collection component with delimiter set as "space" to split your DATEPICKER result which will give you an array with 2 rows (first row:M/D/YYYY; second row:HH:mm:ss) and then split the first row with delimiter "/" which will give you an array of 3 rows (first row : M, second row:D, third row:YYYY) and then use the merge text component to set as you need for your query?

Hope this helps.

Regards.

Cedric DUBIE

Workflow Solution Developper

Altiris Consultant

Hi,

In my opinion the best component for conversion DATATIME data type to STRING is Merge Text. It has a Date Formatter allowing for very flexible field formatting based on .NET rules.

But I doubt that will help.
If your SQL querry requires a DATATIME parameter the text variable you will pass to script component will have to be converted to DATETIME but this conversion will be implicit, based on region and national settings of the system.

If you want have more control over the passed DATETIME values then change the SQL querry to requiring datetime values as STRING data type parameters. In the process model convert the variables from datepickers to STRINGS using Merge Text, pass them to SQL querry component as a STRINGS and in querry you can reconvert it to DATETIME again (or let the query engine for implicit conversion).

Thanks.I did this half way.. I tried this with just spliting the date and of course it didn't work.  I didn't think about spliting the time and date apart.  Seems the SQL query didn't need the time.  So I adjusted the regional settings on the system to military time which Ricardo suggested and then I split the text as you suggested.  That got it working great. 

Now I have to wait for the fall out of changing the date format and see if it's going to affect my other workflows.

Thanks.. I wasn't aware of that functionality.  I have to put that in my bag of tricks for later.