Convert date - SQL Server Forums

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

Convert date

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:
90106
120106
10107

needs to be converted to:
2006-09-01
2006-12-01
2007-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]

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

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

[/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 Architect
SQL 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'
	) 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

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 Architect
SQL 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 Larsson
Helsingborg, Sweden

SQLUSA
Starting Member

28 Posts

Posted - 2007-04-12 : 06:06:03

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

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 Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources