convert long value in date in sql

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 long value in date in sql

Author

Topic

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2011-04-18 : 01:45:36

Dear Member

i have one long value like '734258' i have convert long value in sql date

so how can i do this

thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-18 : 02:27:09

what does that number represent ?

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

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2011-04-18 : 02:37:25

this number is days

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-18 : 02:59:25

the number of days since when ?

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

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2011-04-18 : 03:13:24

the starting date is 1753-01-01 00:00:00.000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-18 : 03:40:16

quote:
Originally posted by amirs

the starting date is 1753-01-01 00:00:00.000

Confirmed ?


select dateadd(day, 734258, '1753-01-01')

Result :
3763-05-02 00:00:00.000

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

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2011-04-18 : 04:03:52

thanks to replay
i have already try this query. but it is wrong result

its actual date is 05/01/2011 of '734258' this value

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-04-18 : 04:15:42

But day difference between 05/01/2011 and 01/01/1753 is just 94352 days
SELECT DATEDIFF(DAY,'1753-01-01','05/01/2011')

--------------------------
http://connectsql.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-18 : 04:17:13

quote:
Originally posted by amirs

thanks to replay
i have already try this query. but it is wrong result

its actual date is 05/01/2011 of '734258' this value

Well, you said the number represent the number of days since 1753-01-01.

So 734258 days since 1753-01-01 is 3763-05-02.

734258 days = 734258 / 365 = 2011 year.

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-18 : 04:22:08

are you using SQL 2005 or 2008 ?

If 2008


select	dateadd (day , 734258 - 1, convert(date, '0001-01-01'))

Result : 2011-05-01

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

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2011-04-18 : 04:53:06

i am using sql2005

select dateadd (day , 734258 - 1, convert(date, '0001-01-01'))

how to write this query in sql2005

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-18 : 05:00:12

minus the number of days between 0001-01-01 and 1753-01-01


select	dateadd (day , 734258 - 639905 - 1, '1753-01-01')

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

amirs
Constraint Violating Yak Guru

260 Posts

Posted - 2011-04-18 : 05:05:40

it is very useful for me
thank you very much all of you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources