Question

SQL date format not recognised by workflow

Version 10 Workflow

Updated April 17, 2023

asked on May 13, 2020 • Show version history

I have to retrieve a date from SQL using Workflow. The format of the SQL date is 'datetimeoffset(7)’ e.g. 2020-04-15 00:00:00.0000000 +00:00.

This format is not recognised by workflow. Workflow returns nothing, not even text, from a basic query using eith Query Data or Custom Query.

This workflow was working OK previouly with an SQL date format of ‘datetime’ e.g. 2020-05-12 00:00:00.000

Workflow Track Tokens shows a blank field for the new date format.

0 0

Replies

replied on May 14, 2020

I ran into the same issue with the time datatype. What worked for me was to use the Customer Query and use the CAST function to turn it into a string. Try CAST( field as varchar(32)) as field.

3 0

replied on May 17, 2020

Thanks Jason,

I have found that, to keep the data format as Date, you can use Custom Query with CONVERT (up to SQL2018) or FORMAT (for later SQL versions). e.g.

SELECT CONVERT (nvarchar,[sentDate],23) as newDate

FROM [yourDB].[dbo].[dateTable]

In this case I used 23 as the date format yyy-MM-dd

1 0

replied on April 17, 2023

I have got the same issue.

I'm executing a stored procedure in which I can't modify its query to convert the datetimeoffset field.

Is there another solution from Laserfiche WorkFlow?

0 0

replied on April 17, 2023

There is not information to guess, but be aware that support for datetimeoffset was only very recently added to ODBC drivers for Microsoft SQL: https://learn.microsoft.com/en-us/sql/relational-databases/native-client-odbc-date-time/data-type-support-for-odbc-date-and-time-improvements?view=sql-server-ver16

1 0

You are not allowed to follow up in this post.

Question

Question

SQL date format not recognised by workflow

Replies

Sign in to reply to this post.