Convert date-string to datetime

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Convert date-string to datetime

Author

Topic

mjgomez
Starting Member

6 Posts

Posted - 2015-01-06 : 05:27:20

I am trying to convert a date-string of this format (DDMMMYY - ex. 06JAN15) to datetime but I keep on getting errors:

codes that I tried:
convert(datetime, '06JAN15', 6)
convert(datetime, '06JAN15', 112)

What date format code should I use for DDMMMYY?

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2015-01-06 : 05:44:50

this works fine
select CONVERT(datetime,'06JAN15',112)

what error are you getting?

Javeed Ahmed
https://www.linkedin.com/pub/javeed-ahmed/25/5b/95

mjgomez
Starting Member

6 Posts

Posted - 2015-01-06 : 05:53:21

Hi ahmeds08,

Yes its working for direct input but when I use the column name, its throwing an error:

cast(convert(varchar, [column_name] , 112) as datetime)

"Conversion failed when converting datetime from character string"

for this, its ok:

cast(convert(varchar, '06JAN15' , 112) as datetime)

Thanks

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2015-01-06 : 05:55:21

what is the datatype of the column?

Javeed Ahmed
https://www.linkedin.com/pub/javeed-ahmed/25/5b/95

mjgomez
Starting Member

6 Posts

Posted - 2015-01-06 : 06:01:05

its varchar(50). Thanks

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-01-06 : 06:01:33

Select Convert(int, convert(varchar(20), [column_name], 112)) -- convert to int

We are the creators of our own reality!

mjgomez
Starting Member

6 Posts

Posted - 2015-01-06 : 06:18:23

it is still throwing an error. its weird that it works for this:
cast(convert(varchar, '06JAN15' , 112) as datetime) and not for this
cast(convert(varchar, JoiningDate , 112) as datetime)

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2015-01-06 : 06:23:15

is the data in the column in same format as 06JAN15

Javeed Ahmed
https://www.linkedin.com/pub/javeed-ahmed/25/5b/95

mjgomez
Starting Member

6 Posts

Posted - 2015-01-06 : 06:33:45

Hi Ahmeds08,
Yes. Its of the same format (05NOV14, 05JUL14, 07MAR14). Could it be an issue of the positioning of the call in the select statement?

Here is a part of my select statement:

SELECT distinct cast(convert(varchar, [JoiningDate], 112) as datetime) sortDate, [JoiningDate], [IdNumber],.....,..
FROM [Table]

I added an alias column = sortdate

Thanks

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2015-01-06 : 08:27:53

strange,the same thing works fine on my system.
Example:the output I get is 2015-01-06 00:00:00.000

Javeed Ahmed
https://www.linkedin.com/pub/javeed-ahmed/25/5b/95

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-01-06 : 08:41:49

you are missing a comma

SELECT distinct cast(convert(varchar, [JoiningDate], 112) as datetime), sortDate, [JoiningDate], [IdNumber],.....,..
FROM [Table]

We are the creators of our own reality!

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2015-01-06 : 08:49:11

SordDate is the alias,that should not give the error

Javeed Ahmed
https://www.linkedin.com/pub/javeed-ahmed/25/5b/95

mjgomez
Starting Member

6 Posts

Posted - 2015-01-07 : 00:04:09

Hi Guys,

Thank you for all the suggestions and help. I already found out what the issue is. I missed to check for badly formatted dates strings. I used isDate() for validation - and it worked ok.

Again Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-01-08 : 03:55:30

Beware of ISDATE and handle it with care http://beyondrelational.com/modules/2/blogs/70/posts/10807/handle-isdate-with-care.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources