SQL CONVERT string to date issue

Question

I have a column with string values like this 05.2015.

I need to get a DATE value out in the format that the first 2 chars are the month and the day is always the 1st...

so in this example it would be 01/05/2015 ... or 2015-05-01 ..depending on the db.

I tried

select CONVERT(VARCHAR(7),"String_Date_column",0) from TABLE;

but I am getting this error:

"No authorized routine named "CONVERT" of type "FUNCTION" having compatible arguments was found.. SQLCODE=-440, SQLSTATE=42884, DRIVER=3.69.56 ".

I am running on dashDB.

It looks like you need to consult the [Datetime scalar functions documentation](http://www.ibm.com/support/knowledgecenter/en/SS6NHC/com.ibm.swg.im.dashdb.sql.ref.doc/doc/r0011043.html?view=kc#r0011043__t11043-5) and ponder using `TO_DATE`. — Andrew Morton, Sep 21 '16 at 10:01

score 1 · Accepted Answer · edited Sep 21 '16 at 11:05

1

TO_DATE (CD."Application_Creation_Period",'mm.yyyy')

works just fine. I needed to add CASE because the column also holds one char values of "#". So the full answer would be:

select case
       when "string_date_column" = '#' then null
       else TO_DATE ("string_date_column",'mm.yyyy')
end as "new date" from table;

edited Sep 21 '16 at 11:05

Andrew Morton

answered Sep 21 '16 at 10:45

Mike Pala

score 0 · Answer 2 · answered Sep 21 '16 at 10:21

0

try this

SELECT cast(substring(String_Date_column,4,4)+substring(String_Date_column,1,2)+'01' as date)

answered Sep 21 '16 at 10:21

Billydan

1

I tried this with `sql server`, on `dashDB` it will be `SUBSTR` instead of `substring` – Billydan Sep 21 '16 at 10:32
I keep getting this error:"A value with data type "SYSIBM.DECFLOAT" cannot be CAST to type "SYSIBM.DATE".. SQLCODE=-461, SQLSTATE=42846, DRIVER=3.69.56 " but perharps that's becasue that column also hold values which equal to "#".... anyway. I found another way to get thi done... will post as answer – Mike Pala Sep 21 '16 at 10:43

2 Answers2