Convert numeric date to DateTime

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 2005 Forums

Transact-SQL (2005)

Convert numeric date to DateTime

Author

Topic

Trekmp
Starting Member

6 Posts

Posted - 2009-12-02 : 09:44:17

Hi

I've been given the task of re-developing an old system and bringing it more update to date. I cannot amend the core data, but I can create views, stored procedures, etc.

The dates are currently stored as integers (why I've no idea). So for example:

39644 = 15 July 2008
40147 = 30 Nov 2009
40148 = 01 Dec 2009

Now I'm developing in ASP.NET and can convert these dates (OADate)easy enough this way, but I need to be able to convert them within SQL (views/stored procs) for various reasons, but I cannot find a good way.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-02 : 09:46:03

select col,dateadd(day,col,0) as dates from your_table

Madhivanan

Failing to plan is Planning to fail

Trekmp
Starting Member

6 Posts

Posted - 2009-12-02 : 10:03:36

Hi,

I've tried that, but 39644 comes out as the 17 July 2008 instead of 15 July 2008? Most seem to come out 2 days more than what they should be, but not all.

Thanks for any help provided.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-02 : 10:19:01

quote:
Originally posted by Trekmp

Hi,

I've tried that, but 39644 comes out as the 17 July 2008 instead of 15 July 2008? Most seem to come out 2 days more than what they should be, but not all.

Thanks for any help provided.

Then what is the rule?

select col,dateadd(day,col-2,0) as dates from your_table

Madhivanan

Failing to plan is Planning to fail

Trekmp
Starting Member

6 Posts

Posted - 2009-12-02 : 10:33:14

That won't work either, as not all dates need to have the -2 taken off. It this that has got me beat. It only seems to be a very small portion of dates that do not require the -2, but I cannot seem to find out why these dates don't.

Thanks for any help provided.

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2009-12-02 : 11:01:45

I just want to point out that SQL 2005 starts on 1/1/1900 and Excel (which I think is equivelent to you ASP.NET) starts on 1/0/1900. (Check this by putting 0 into a cell and convert to date)

Therefor you are already off by 1 day. I was able to match your 3 dates above by using...change the values before the operands is all

declare @a int
declare @b int
set @a = 40148/365.25
set @b = 40148%365.25
Select @a,@b
select dateadd(day,-1,dateadd(Year, @a, dateadd(day,@b,0)))

--This part shows that SQL starts on 1/1/1900
select dateadd(day,0,0)

Trekmp
Starting Member

6 Posts

Posted - 2009-12-03 : 04:18:41

Thought we were onto a winner with that one, but it doesn't seem to like leap years.

declare @a int
declare @b int
set @a = 39801/365.25
set @b = 39801%365.25
Select @a,@b
select dateadd(day,-1,dateadd(Year, @a, dateadd(day,@b,0)))

This date should return 19/12/2008, but it gives me 20/12/2008

Thanks for any help provided.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-12-03 : 05:11:18

[code]
select dateadd(day, 39801, -2)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources