SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Convert date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

simmark333
Starting Member

5 Posts

Posted - 04/10/2007 :  18:09:46  Show Profile  Reply with Quote
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:
90106
120106
10107

needs to be converted to:
2006-09-01
2006-12-01
2007-01-01

khtan
In (Som, Ni, Yak)

Singapore
17658 Posts

Posted - 04/10/2007 :  18:46:07  Show Profile  Reply with Quote

dateadd(day, left(right(datecol, 4), 2) - 1, dateadd(month, left(datecol, len(datecol) - 4) - 1, dateadd(year, 2000 - 1900 + right(datecol,2), 0)))



KH

Go to Top of Page

dinakar
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 04/10/2007 :  18:46:10  Show Profile  Visit dinakar's Homepage  Reply with Quote
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/
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17658 Posts

Posted - 04/10/2007 :  18:47:28  Show Profile  Reply with Quote
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

Go to Top of Page

simmark333
Starting Member

5 Posts

Posted - 04/10/2007 :  18:49:11  Show Profile  Reply with Quote
Thanks guys!
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/10/2007 :  19:46:59  Show Profile  Reply with Quote


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'
	) a


Results:
dt     Date 
------ -----------------------
90106  2006-09-01 00:00:00.000
120106 2006-12-01 00:00:00.000
10107  2007-01-01 00:00:00.000

(3 row(s) affected)




CODO ERGO SUM
Go to Top of Page

dinakar
Flowing Fount of Yak Knowledge

USA
2507 Posts

Posted - 04/10/2007 :  19:51:17  Show Profile  Visit dinakar's Homepage  Reply with Quote
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/
Go to Top of Page

SQLUSA
Starting Member

28 Posts

Posted - 04/10/2007 :  20:37:56  Show Profile  Visit SQLUSA's Homepage  Reply with Quote
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 Architect
SQL Server 2005 Training - http://www.sqlusa.com
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 04/10/2007 :  23:20:14  Show Profile  Reply with Quote
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'
	) a



Results:

dt     Date
------ -----------------------
90106  2006-09-01 00:00:00.000
90196  1996-09-01 00:00:00.000
120106 2006-12-01 00:00:00.000
120196 1996-12-01 00:00:00.000
10107  2007-01-01 00:00:00.000

(5 row(s) affected)











CODO ERGO SUM

Edited by - Michael Valentine Jones on 04/10/2007 23:27:02
Go to Top of Page

SQLUSA
Starting Member

28 Posts

Posted - 04/11/2007 :  22:08:40  Show Profile  Visit SQLUSA's Homepage  Reply with Quote
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 Architect
SQL Server 2005 Training - http://www.sqlusa.com
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30276 Posts

Posted - 04/12/2007 :  01:26:41  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 Larsson
Helsingborg, Sweden
Go to Top of Page

SQLUSA
Starting Member

28 Posts

Posted - 04/12/2007 :  06:06:03  Show Profile  Visit SQLUSA's Homepage  Reply with Quote
That depends on your regions setting on your computer.

America: MMDDYY

Europe: DDMMYY



Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30276 Posts

Posted - 04/12/2007 :  06:09:20  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 Larsson
Helsingborg, Sweden

Edited by - SwePeso on 04/12/2007 06:14:14
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000