dateadd and convert - 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)

dateadd and convert

Author

Topic

aerosmith
Starting Member

9 Posts

Posted - 2011-02-15 : 12:25:07

Good Morning

Im trying to find a way to cast a unixtime stamp to datetime, and convert it to a formated option in am/pm

This convert option seems to be converting the current date in the appropirate maner

replace(convert(varchar(8), getdate(), 10), '-', '/') + ' ' +substring(convert(varchar(20), getdate(), 9), 13, 5) + ' ' + substring(convert(varchar(30), getdate(), 9), 25, 2)

And i need to use the following cast to convert the unix time from the field to datetime.

cast(cast(dateadd(ss,[SLA_Start_Time],'1970-01-01') as int) as datetime) as SLA_Start_Time

Im having trouble combining them both together, cant seem to get what i want.

As an end result, i would want something to look like this

02/15/11 12:24 PM

KlausEngel
Yak Posting Veteran

85 Posts

Posted - 2011-02-15 : 12:33:36

select replace(convert(varchar(8), (cast(cast(dateadd(ss,SLA_Start_Time,'1970-01-01') as int) as datetime)), 10), '-', '/') + ' ' +substring(convert(varchar(20), getdate(), 9), 13, 5) + ' ' + substring(convert(varchar(30), getdate(), 9), 25, 2)

aerosmith
Starting Member

9 Posts

Posted - 2011-02-15 : 12:38:40

Holy crap it works :) thank you so much

aerosmith
Starting Member

9 Posts

Posted - 2011-02-16 : 15:47:31

Now that i check it, its not converting the time,
it shows the same time for ever record.

replace(convert(varchar(8), (cast(cast(dateadd(ss,Create_Date,'1970-01-01') as int) as datetime)), 10), '-', '/') + ' ' +substring(convert(varchar(20), getdate(), 9), 13, 5) + ' ' + substring(convert(varchar(30), getdate(), 109), 25, 2) as Create_Date

The Dates are fine, But the Times are not.
PMA000000000044 05/15/07 3:45 PM
PMA000000000045 05/15/07 3:45 PM

Any ideas

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2011-02-16 : 16:49:22

Here are a couple of examples:


 Select datediff(second, '19700101', '20110215 12:24')   --1297772640

 Select convert(char(19), dateadd(second, 1297772640, '19700101'), 120)
      , convert(char(11), dateadd(second, 1297772640, '19700101'), 101) 
      + convert(char(12), dateadd(second, 1297772640, '19700101'), 108)
      , convert(char(11), dateadd(second, 1297772640, '19700101'), 101) 
      + right(dateadd(second, 1297772640, '19700101'), 7)

Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources