cancel
Showing results for 
Search instead for 
Did you mean: 

How do I convert a string to date in IDT with SQL server as backend?

Former Member
0 Kudos

Hi,

I have a need to convert string to date in the IDT.

The backend data source is SQL Server. My BO version is 4.1.

Cast() is not supported in IDT SQL expression editor.

Also, Convert() has limitations. It cannot recognize - DATE or DATETIME types in IDT.

Can anyone suggest me a workaround?

Situation: User answers a prompt - MonthYear.

Using this, I have to roll back to Month Year of previous year.

Eg., User input, MonthYear = 2016-05

then expected result, PrevMonthYear= 2015-05.

To achieve this, I need to manipulate the user response value.

No date function supports a part of date (yyyy-mm).

So, the need is to cast 2016-05 as 2016-05-01 as date value.

Then yyyy-1, to get 2015-05-01. Later, truncate it to get 2015-05.


X = concat(convert(varchar(10), userinput), '-01') --- gets me '2016-05-01'

then I have to convert above string X to Date to execute below dateadd function.

dateadd(yyyy, -1, X) --- gets me 2015-05-01as required.


Please suggest a workaround for conversion of string TO date.

Many thanks

Accepted Solutions (0)

Answers (1)

Answers (1)

mhmohammed
Active Contributor
0 Kudos

Hi Rashmi,

Try the To_date() function, check this out:

http://stackoverflow.com/questions/207190/sql-server-string-to-date-conversion

And then, as you want to minus 1 Year from that date, use the Dateadd() function:

dateadd(year,-Y,getdate())

http://stackoverflow.com/questions/4247060/sql-subtract-exactly-a-year


Thanks,

Mahboob Mohammed