Author |
Topic |
simmark333
Starting Member
5 Posts |
Posted - 2007-04-10 : 18:09:46
|
I need to convert the date below to the SQL datetime. The single month dates do not start with a '0'.Can anyone please provide me with a script to do this? Thank you in advance!ex:9010612010610107needs to be converted to:2006-09-012006-12-012007-01-01 |
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-04-10 : 18:46:07
|
[code]dateadd(day, left(right(datecol, 4), 2) - 1, dateadd(month, left(datecol, len(datecol) - 4) - 1, dateadd(year, 2000 - 1900 + right(datecol,2), 0)))[/code] KH |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-04-10 : 18:46:10
|
Declare @t varchar(10)set @t = '90106'set @t = case when len(@t) = 6 then @t when len(@t) = 5 then '0' + @t end set @t = right(@t,2) + left(@t,4)select convert(varchar,convert(datetime, @t ),101)************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-04-10 : 18:47:28
|
quote: Originally posted by dinakar Declare @t varchar(10)set @t = '90106'set @t = case when len(@t) = 6 then @t when len(@t) = 5 then '0' + @t end set @t = right(@t,2) + left(@t,4)select convert(varchar,convert(datetime, @t ),101)************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/
this is much simpler KH |
|
|
simmark333
Starting Member
5 Posts |
Posted - 2007-04-10 : 18:49:11
|
Thanks guys! |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-04-10 : 19:46:59
|
[code]select dt, Date=convert(datetime,'20'+right(dt,2)+left(right('00'+dt,6),4))from ( -- Test Data select dt = '90106' union all select '120106' union all select '10107' ) aResults:dt Date ------ -----------------------90106 2006-09-01 00:00:00.000120106 2006-12-01 00:00:00.00010107 2007-01-01 00:00:00.000(3 row(s) affected)[/code]CODO ERGO SUM |
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2007-04-10 : 19:51:17
|
Michael your code works if the year is > 2000. Try it with sample data of '90196' and your code will return '2096-09-01' instead of '1996-09-01'. 96 by itself is kinda ambigious..but the intention I believe is 1996.************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
SQLUSA
Starting Member
28 Posts |
Posted - 2007-04-10 : 20:37:56
|
declare @date varchar(6)set @date = '10107'select convert(datetime, stuff(stuff(right('0'+@date,6),5,0,'-'),3,0,'-'))Kalman Toth, Database, DW & BI ArchitectSQL Server 2005 Training - http://www.sqlusa.com |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-04-10 : 23:20:14
|
quote: Originally posted by dinakar Michael your code works if the year is > 2000. Try it with sample data of '90196' and your code will return '2096-09-01' instead of '1996-09-01'. 96 by itself is kinda ambigious..but the intention I believe is 1996.************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/
I know, but I did not see any 96 in the test data, so my assumption was that the dates are recent.However, the following code will handle the 96 as the year 1996 by just removing the '20' from the code I posted before.select dt, Date=convert(datetime,right(dt,2)+left(right('00'+dt,6),4))from ( -- Test Data select dt = '90106' union all select '90196' union all select '120106' union all select '120196' union all select '10107' ) aResults:dt Date------ -----------------------90106 2006-09-01 00:00:00.00090196 1996-09-01 00:00:00.000120106 2006-12-01 00:00:00.000120196 1996-12-01 00:00:00.00010107 2007-01-01 00:00:00.000(5 row(s) affected) CODO ERGO SUM |
|
|
SQLUSA
Starting Member
28 Posts |
Posted - 2007-04-11 : 22:08:40
|
Try this:declare @date varchar(6)set @date = '10196'select convert(datetime, stuff(stuff(right('0'+@date,6),5,0,'-'),3,0,'-'))Kalman Toth, Database, DW & BI ArchitectSQL Server 2005 Training - http://www.sqlusa.com |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-04-12 : 01:26:41
|
I used this test date:set @date = '10496'SQLUSA: With your code I get "April 1, 1996". Should be "Jan 4, 1996"MVJ: As usual, I get the correct date; "Jan 4, 1996"Peter LarssonHelsingborg, Sweden |
|
|
SQLUSA
Starting Member
28 Posts |
Posted - 2007-04-12 : 06:06:03
|
That depends on your regions setting on your computer.America: MMDDYYEurope: DDMMYYKalman Toth, Database, DW & BI ArchitectSQL Server 2005 Training - http://www.sqlusa.com |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-04-12 : 06:09:20
|
Yes, I know. We have discussed this over a period of time here.I tend to lean against solutions that do NOT make use of regional settings.If you do as MVJ suggest, use ISO DATE FORMAT (YY)YYMMDD, you can use the suggestion anywhere.Remember, this forum (and other) is WORLDWIDE.Peter LarssonHelsingborg, Sweden |
|
|
|