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/

Go to Top of Page

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/



Go to Top of Page

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,'-'))
Go to Top of Page

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


Go to Top of Page

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/

Go to Top of Page

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

Go to Top of Page
   

- Advertisement -