convert decimal date (8,0) to Date

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

SQL Server 2000 Forums

Transact-SQL (2000)

convert decimal date (8,0) to Date

Author

Topic

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2011-09-29 : 08:08:21

Hello,

I need help to convert decimal date to Date, such as: decimal date = 010613 that need to display as 2001-06-13

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-29 : 08:24:54

you can do like

SELECT CONVERT(datetime,CASE WHEN datefield/10000 >= 50 then 19000000 else 20000000 end + datefield,112) FROM table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2011-09-29 : 09:43:23

It does not work...there is an error message: "Convert argument does not match the required data type"

SELECT CONVERT(datetime,CASE WHEN fdate/10000 >= 50 then 19000000 else 20000000 end + fdate,112) FROM table1

DATA TYPE OF FDATE = DECIMAL(8,0)

Any idea? thanks,

quote:
Originally posted by visakh16

you can do like

SELECT CONVERT(datetime,CASE WHEN datefield/10000 >= 50 then 19000000 else 20000000 end + datefield,112) FROM table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Reporter
Starting Member

48 Posts

Posted - 2011-09-29 : 10:28:52

declare @dat decimal(8,0)

set @dat=010613
select convert(datetime,stuff(stuff(right('200'+convert(varchar(8),@dat),8),5,0,'-'),8,0,'-'))

set @dat=19750319
select convert(datetime,stuff(stuff(right('200'+convert(varchar(8),@dat),8),5,0,'-'),8,0,'-'))

nt4vn
Yak Posting Veteran

98 Posts

Posted - 2011-09-29 : 11:19:04

Yes, it works for that specific case,
but when I run for the whole, it does not work...since it 's not only start with '200'. What can I do to fix this?

Thanks,

quote:
Originally posted by Reporter

declare @dat decimal(8,0)

set @dat=010613
select convert(datetime,stuff(stuff(right('200'+convert(varchar(8),@dat),8),5,0,'-'),8,0,'-'))

set @dat=19750319
select convert(datetime,stuff(stuff(right('200'+convert(varchar(8),@dat),8),5,0,'-'),8,0,'-'))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-29 : 12:59:44

SELECT CONVERT(datetime,CAST(datefield as varchar(6),112) FROM table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Reporter
Starting Member

48 Posts

Posted - 2011-09-30 : 01:01:30

???

set @dat=19750319
select convert(datetime,stuff(stuff(right('200'+convert(varchar(8),@dat),8),5,0,'-'),8,0,'-'))

... it 's not start with '200' ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources