update char column than convert to date column

Question

Post reply

update char column than convert to date column

denimblue

Mr or Mrs. 500

Points: 594
More actions
January 14, 2008 at 12:52 pm

#95781

column (char) s_dob exist with various data example:
m/d/yy 1/2/90
mm/d/yy 11/1/91
m/dd/yy 1/02/95
mm/dd/yy 10/11/94
m/d/yyyy 1/3/1991
mm/d/yyyy 12/5/1986
m/dd/yyyy 6/30/2000
mm/dd/yyyy 12/12/2001
Would like to update data to a mm/dd/yyyy format and then convert the column to a date format.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

don't bother - CAST will convert all of them correctly.

select cast(dte as datetime)

from

(select '1/2/90' as dte UNION ALL

select '11/1/91' as dte UNION ALL

select '1/02/05' as dte UNION ALL

select '1/02/95' as dte UNION ALL

select '10/11/94' as dte UNION ALL

select '1/3/1991' as dte UNION ALL

select '12/5/1986' as dte UNION ALL

select '6/30/2000' as dte UNION ALL

select '12/21/1990' as dte) as t

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

denimblue Mr or Mrs. 500 Points: 594 More actions · Answer 2

How can an update work for changing just the two digit year to four without changing the month or day?

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

denimblue (1/14/2008)
How can an update work for changing just the two digit year to four without changing the month or day?

When you tried the posted code, did it work correctly?

denimblue Mr or Mrs. 500 Points: 594 More actions · Answer 4

No,

I didn't try it, because the dates given were not real. I have many rows of different data in various formats. I would like them all to be in the same format 'mm/dd/yyyy.

m/d/yy, m/dd/yy, mm/d/yy

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

denimblue (1/14/2008)
No,
I didn't try it, because the dates given were not real. I have many rows of different data in various formats. I would like them all to be in the same format 'mm/dd/yyyy.
m/d/yy, m/dd/yy, mm/d/yy

So you just assumed the code would not work without testing it?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 6

Well - I was assuming you were giving us a reasonably complete picture of the formats you were going to have. The point was - the CAST predicate is VERY good at converting any number of formats.

You mentioned wanting to end up with a datetime value (and not a char field). In the absence of any other data, you don't need to do anything intermediate. You really SHOULD try it against some of your data. Really - it converts pretty well. It even converts stuff in YYYYMMDD format.

You do realize that SQL server has some things built in to handle correctly converting most 2-digit dates to four (anything = goes to 19)?

That being said - if you're hell-bent on dfoing this the hard way, or if you should need to override the default 2-year handler for some reason, here's something to get you started.

drop table t1

select * into t1

from

(select '1/2/90' as dte UNION ALL

select '11/1/91' as dte UNION ALL

select '1/02/05' as dte UNION ALL

select '1/02/95' as dte UNION ALL

select '10/11/94' as dte UNION ALL

select '1/3/1991' as dte UNION ALL

select '12/5/1986' as dte UNION ALL

select '6/30/2000' as dte UNION ALL

select '12/21/1990' as dte) as t

update t1

set dte='0'+dte

where patindex('%/%',dte)=2

update t1

set dte=left(dte,3)+'0'+substring(dte,4,15)

where patindex('%/%',substring(dte,4,15))=2

update t1

set dte=left(dte,6)+case when substring(dte,7,15)<50 then '20' else '19' end +substring(dte,7,15)

where len(dte)<>10

Of course - if this script works for you once you've put in your own table, then my original comment still stands....CAST would work better. Otherwise - you'll have to share with the rest of the class what special twist you were planning on doing to this (which would have been nice in your original post).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

denimblue Mr or Mrs. 500 Points: 594 More actions · Answer 7

select cast(dte as datetime)

from stutable1

(select '1/2/90' as dte UNION ALL

select '11/1/91' as dte UNION ALL

select '1/02/05' as dte UNION ALL

select '1/02/95' as dte UNION ALL

select '10/11/94' as dte UNION ALL

select '1/3/1991' as dte UNION ALL

select '12/5/1986' as dte UNION ALL

select '6/30/2000' as dte UNION ALL

select '12/21/1990' as dte) as stutable1

Server: Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'select'.

Server: Msg 170, Level 15, State 1, Line 11

Line 11: Incorrect syntax near ')'.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 8

denimblue (1/14/2008)
select cast(dte as datetime)
from stutable1
(select '1/2/90' as dte UNION ALL
select '11/1/91' as dte UNION ALL
select '1/02/05' as dte UNION ALL
select '1/02/95' as dte UNION ALL
select '10/11/94' as dte UNION ALL
select '1/3/1991' as dte UNION ALL
select '12/5/1986' as dte UNION ALL
select '6/30/2000' as dte UNION ALL
select '12/21/1990' as dte) as stutable1
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'select'.
Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near ')'.

Why don't you just try the code Matt Miller posted? You added the error when you changed it.