July 26, 2002 at 11:02 am
Folks,
I have a legacy column that has dates as characters in a column. I would like to convert these characters to actual dates stored in a dateime column. The characters are in the following format: 10869
This means January 8, 1969.
There are approximately 15,000 records, each with a five or six number character string in the old "date" column. For two digit months, the format looks like 120569 for December 5, 1969.
Any ideas on how to convert? I was playing with the CONVERT function and was getting some really wierd results. The datetime column in formatted in US style for four digits for the year.
Thanks in advance,
Jeff
July 26, 2002 at 11:25 am
use substring to parse the data into a certain format and build the string that you want. Then use convert.
Steve Jones
July 26, 2002 at 11:32 am
Steve,
Do you have a recommended format to substring? I was playing with various values and wasn't able to get the convert to work on converting any text to a meaningful date. By chance would you have an example that converts a character string to a smalldate using the US style?
Thanks again,
Jeff
July 26, 2002 at 11:53 am
something like
select case when len(col) = 5 then substring(col,1,2)
else substring(col,1,1)
end 'month'
continue with the same to contatenate the day and year along with '/'. Then do a convert on the whole thing.
Steve Jones
July 26, 2002 at 2:02 pm
You don't need to use convert if you create a string correctly formatted to your default date format setting, and then assign it directly to the datetime column.
Here's an example, assuming US defaults:
create table test (
textdate char(6) NULL,
realdate smalldatetime NULL
)
insert test (textdate) values ('10898') -- Jan 8, 1998
insert test (textdate) values ('102802') -- Oct 28, 2002
update test set realdate =
case when len(textdate) = 5
then
substring(textdate,1,1)+'/'+substring(textdate,2,2)+'/'+substring(textdate,4,2)
else
substring(textdate,1,2)+'/'+substring(textdate,3,2)+'/'+substring(textdate,5,2)
end
select * from test
Jay
Jay Madren
July 26, 2002 at 2:36 pm
Steve and Jay -
Thank you so much for your help. Steve - I was able to get your thoughts to work, however it did take some time. Jay - I was able to get yours to work (almost). One more question, any ideas how to deal with a case where some of the "day" is 00? I would just like to treat them all as a 01.
I have been able to get it to work if I actually parse out each part into its own column and then concat them back together. However this was time consuming. Any ideas on how to incorporate this check into your original script?
Thanks again!
jeff
July 26, 2002 at 3:27 pm
You can nest case structures. Here's my previous example ammended to handle the 00 day:
create table test (
textdate char(6) NULL,
realdate smalldatetime NULL
)
insert test (textdate) values ('10898') -- Jan 8, 1998
insert test (textdate) values ('102802') -- Oct 28, 2002
insert test (textdate) values ('120002') -- 00 day, set it to 01: Dec 01, 2002
update test set realdate =
case when len(textdate) = 5
then
substring(textdate,1,1)+'/'+ case when substring(textdate,2,2) = '00'
then '01' else substring(textdate,2,2) end +'/'+substring(textdate,4,2)
else
substring(textdate,1,2)+'/'+ case when substring(textdate,3,2) = '00'
then '01' else substring(textdate,3,2) end +'/'+substring(textdate,5,2)
end
select * from test
Jay
Jay Madren
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply