Converting Apr 25, 2009 or Jun 1, 2009 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

Converting Apr 25, 2009 or Jun 1, 2009 to datetime

Author

Topic

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-13 : 11:58:14

I have a column full of dates in the format of
Apr 23, 2009
Jun 4, 2010
Dec 21, 2009

I was wondering, how do I go about turning this into your standard datetime or smalldatetime that looks like

4/23/2009 12:00:00 AM
6/4/2010 12:00:00 AM
12/21/2009 12:00:00 AM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-13 : 12:02:04

what is the data type of that column ?

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 12:02:38

is it having datatype of datetime?

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-13 : 12:03:14

the original datatype of the column is nvarchar(4000) (pretty ridiculous right? i didnt design the db)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-13 : 12:04:26

use convert(datetime, yourcol, 100) to convert to datetime

KH
[spoiler]Time is always against us[/spoiler]

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-13 : 13:24:04

quote:
Originally posted by khtan

use convert(datetime, yourcol, 100) to convert to datetime

KH
[spoiler]Time is always against us[/spoiler]

I keep getting

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.

Does this mean that at least one entry in my date column is not in this...

Apr 21, 2008
Jun 1, 2009

format??

How does one even start to look for the odd man out, there's hundreds of thousands of rows.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 13:29:50

Try this

SELECT * FROM YourTable WHERE ISDATE(datecolumn)=0 OR LEN(datecolumn)<=4

to check if you've some spurious date values

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-13 : 13:41:40

quote:
Originally posted by visakh16

Try this

SELECT * FROM YourTable WHERE ISDATE(datecolumn)=0 OR LEN(datecolumn)<=4

to check if you've some spurious date values

I ran that query and received 13,000 entries

I'm looking at them and they look fine though,

there are spaces at the end but I have ltrim(rtrim(

going so that shouldnt matter right?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 13:43:08

Nope. they shouldnt matter as long as you're trimming. what about this?

SELECT * FROM YourTable WHERE ISDATE(LTRIM(RTRIM(datecolumn)))=0 OR LEN(LTRIM(RTRIM(datecolumn)))<=4

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-13 : 13:46:53

quote:
Originally posted by visakh16

Nope. they shouldnt matter as long as you're trimming. what about this?

SELECT * FROM YourTable WHERE ISDATE(LTRIM(RTRIM(datecolumn)))=0 OR LEN(LTRIM(RTRIM(datecolumn)))<=4

this query resulted in the exact same sample set of 13195 entries

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-13 : 13:49:48

I don't think the trim functions are working for these 13195 entries

Because I just did a group by saildate

and for some dates there are two entries

for example

Apr 1, 2010
Apr 1, 2010

the first one has no space, the second one has a 2 spaces (the ones that are in the 13195 that is messing up the datatype conversion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 13:52:29

quote:
Originally posted by sqlchiq

I don't think the trim functions are working for these 13195 entries

Because I just did a group by saildate

and for some dates there are two entries

for example

Apr 1, 2010
Apr 1, 2010

the first one has no space, the second one has a 2 spaces (the ones that are in the 13195 that is messing up the datatype conversion

are you sure there are spaces. is there a chance that they might be some unprintable characters?

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-13 : 13:53:57

quote:
Originally posted by visakh16

quote:
Originally posted by sqlchiq

I don't think the trim functions are working for these 13195 entries

Because I just did a group by saildate

and for some dates there are two entries

for example

Apr 1, 2010
Apr 1, 2010

the first one has no space, the second one has a 2 spaces (the ones that are in the 13195 that is messing up the datatype conversion

are you sure there are spaces. is there a chance that they might be some unprintable characters?

Ahhhh, i think you're right, on my local machine they appear as spaces, but on the server they appear as those unprintable character boxes

What can I do? I can't even see what they are

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-13 : 13:59:20

It looks like there are always 2 unprintable characters after every date in those 13195 entries.

is there a function to remove the last 2 characters from a string?

I can't think of one off the top, and my searches only lead me to trim which only deals with spaces

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-13 : 14:02:34

try this and see if it works

SELECT CONVERT(datetime,SUBSTRING(datecolumn,CHARINDEX(' ',datecolumn)+1,2)+' '+LEFT(datecolumn,3)+' '+SUBSTRING(datecolumn,CHARINDEX(',',datecolumn)+2,4))

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-08-13 : 15:06:32

quote:

Originally posted by visakh16

try this and see if it works

SELECT CONVERT(datetime,SUBSTRING(datecolumn,CHARINDEX(' ',datecolumn)+1,2)+' '+LEFT(datecolumn,3)+' '+SUBSTRING(datecolumn,CHARINDEX(',',datecolumn)+2,4))

Yup, it did! thank you visakh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources