I am using this old post because I have exactly a situation mentioned in the last post. In my table I a have a VARCHAR(50) column where date is coming as May 8, 2012 and January 17, 2012.
How can I cast this column.
If the format is always correct and the only problem is the single digit day you need to do some CASE/SUBSTRING:
WHEN x LIKE '% _,%'
THEN SUBSTRING(x FROM 1 FOR POSITION(',' IN x) - 2) || '0' || SUBSTRING(x FROM POSITION(',' IN x) - 1)
END (DATE, FORMAT 'mmmmBdd,Byyyy')
I have similar question. I need to convert a varchar like this 8/12/2012 into DATE format. I am getting an error message that this is not a valid date.
I appreciate your help.
Thank you for your response. This worked well when the date is like '8/17/2012', however I also have dates like '8/7/2012' or '10/7/2012'. I.e. each of the day or month can be one or two digits in my input. Is there a more general way to address this? If not how can I use this method to add '0' to the month?
Many thanks again for the hlep.
You can check the length of the date string and convert it accordingly...
SELECT '8/12/2012' AS COL1
, CASE WHEN CHAR_LENGTH(COL1) = 9 THEN CAST('0'||COL1 AS DATE FORMAT 'DD-MM-YYYY')
ELSE CAST(COL1 AS DATE FORMAT 'DD-MM-YYYY')