CODEWITHSUNDEEP
sqldashdb
Mike Pala
Mike Pala

Reputation: 806

SQL CONVERT string to date issue

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.

Upvotes: 0

Views: 1402

Answers (2)

Mike Pala
Mike Pala

Reputation: 806

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;

Upvotes: 1

Billydan
Billydan

Reputation: 485

try this 

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

Upvotes: 0

Related Questions