Converting Char string to dateTime issue

Question

Converting Char string to dateTime issue

gabCruz

Grasshopper

Points: 20
More actions
January 6, 2015 at 6:07 am

#150797

I am developing a stored proc that retrieves from a table with a varchar(50) column named "JoiningDate". The values are of this format "DDMONYY". Sample values are "06JAN15", "04MAR14", "30MAY13"..
So I can sort the table, I added an additional column (sortDate) in the select statement where its value is a conversion of the JoiningDate values to datetime.
SELECT distinct convert(datetime, cast(JoiningDate as varchar(50)), 112) sortDate, [JoiningDate], [Designation], [Role], [Grade]
FROM Employees2
I am receiving this error: Conversion failed when converting datetime from character string.
The weird thing is when input values directly like this (see below), its working.. Am I missing some function or code?
SELECT distinct convert(datetime, cast('06JAN15' as varchar(50)), 112) sortDate, [JoiningDate], [Designation], [Role], [Grade]
FROM Employees2
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

gabCruz (1/6/2015)
I am developing a stored proc that retrieves from a table with a varchar(50) column named "JoiningDate". The values are of this format "DDMONYY". Sample values are "06JAN15", "04MAR14", "30MAY13"..
So I can sort the table, I added an additional column (sortDate) in the select statement where its value is a conversion of the JoiningDate values to datetime.
SELECT distinct convert(datetime, cast(JoiningDate as varchar(50)), 112) sortDate, [JoiningDate], [Designation], [Role], [Grade]
FROM Employees2
I am receiving this error: Conversion failed when converting datetime from character string.
The weird thing is when input values directly like this (see below), its working.. Am I missing some function or code?
SELECT distinct convert(datetime, cast('06JAN15' as varchar(50)), 112) sortDate, [JoiningDate], [Designation], [Role], [Grade]
FROM Employees2
Thanks

You want style code 6 for this: http://msdn.microsoft.com/en-us/library/ms187928.aspx and you will have to insert two spaces into the string to separate the elements.

Storing dates as text always sucks. Bigtime.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

You don't need to do anything to it but convert it to a DATETIME. And, you don't need to insert any spaces. SQL Server is pretty tolerant when it comes to such conversions. Try the following and see for yourself.

SELECT CONVERT(DATETIME,'06JAN15');

If you're an ANSI/ISO purist, the following also works just fine.

SELECT CAST('06JAN15' AS DATETIME);

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Jeff Moden (1/6/2015)
You don't need to do anything to it but convert it to a DATETIME. And, you don't need to insert any spaces. SQL Server is pretty tolerant when it comes to such conversions. Try the following and see for yourself.
SELECT CONVERT(DATETIME,'06JAN15');
If you're an ANSI/ISO purist, the following also works just fine.
SELECT CAST('06JAN15' AS DATETIME);

That's interesting. This - CONVERT(DATETIME,'06JAN15') - corresponds to the very first row in the datetime CONVERT table in BOL, and should input (or output) mon dd yy:

SELECT CONVERT(DATE,'JAN 06 15'); -- mon dd yy per BOL

SELECT CONVERT(DATE,'06JAN15'); -- not listed

You don't see '20150106' in there either.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

gabCruz Grasshopper Points: 20 More actions · Answer 4

Hi Gents,

Thanks for all the suggestions. I already found out what the issue is. I missed to validate the date strings using isDate(). My bad. I used isDate and it worked ok.

Apologies and Thanks.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

gabCruz (1/6/2015)
Hi Gents,
Thanks for all the suggestions. I already found out what the issue is. I missed to validate the date strings using isDate(). My bad. I used isDate and it worked ok.
Apologies and Thanks.

You might want to be a bit careful about using just ISDATE(). What do you thing a string containing '5000' as a string is going to return???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)