convert from int to 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

SQL Server 2005 Forums

Transact-SQL (2005)

convert from int to date

Author

Topic

apple
Starting Member

3 Posts

Posted - 2009-02-05 : 01:48:23

Hi,
Appreciate your help on this task..

I have columns contains value supposed to be in date type. But somehow it is stored in integer

select mydate from mytable;

mydate
-------

1206670655
1206677090
1206677527

I need to view the actual date. Can anyone help me?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-05 : 02:11:32

try like this
declare @t int
set @t = 1206670655
select @t
select dateadd(ss,@t,'1/1/1970')

select dateadd(ss,urcolumnname,'1/1/1970') from urtable

apple
Starting Member

3 Posts

Posted - 2009-02-05 : 02:49:38

Hi bklr,

The date match but not the time. Any ideas on getting the correct time also?

Thks in advance!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-05 : 03:27:36

What date AND time do the three sample data represent?

E 12°55'05.63"
N 56°04'39.26"

apple
Starting Member

3 Posts

Posted - 2009-02-05 : 03:37:23

Here you go..
After i run this sql;

select mydate,dateadd(ss,mydate,'1/1/1970 ')newdate from mytable;

mydate newdate
1206670655 28/03/2008 2:17:35
1206677090 28/03/2008 4:04:50
1206677527 28/03/2008 4:12:07

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-05 : 03:50:13

But you wrote the time is not correct. The date is correct.
What should the time be?

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-05 : 05:14:28

for ur data mydate
-------
1206670655
1206677090
1206677527
give us expected date? and time? for every record

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources