convert date string - 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 2000 Forums

SQL Server Development (2000)

convert date string

Author

Topic

tonyz
Starting Member

5 Posts

Posted - 2007-07-04 : 06:18:53

hi everyone

I need to convert date stored as string(varchar) to seconds(int) before I can do some calculations

the string date looks like this:

01/12/2007 10:00:00

any ideas?
thnx
tony

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-04 : 06:28:19

So what is the expected output?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-04 : 06:32:10

[code]declare @dt datetime
set @dt = '01/12/2007 10:00:00'

select datediff(second, dateadd(day, datediff(day, 0, @dt), 0), @dt) as secondswithinday[/code]

Peter Larsson
Helsingborg, Sweden

tonyz
Starting Member

5 Posts

Posted - 2007-07-04 : 06:42:33

the app stores dates as unix seconds (seconds since 1/1/1970)

so, looking to convert the string to seconds since 1/1/1970

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-04 : 06:45:44

[code]declare @dt datetime
set @dt = '01/12/2007 10:00:00'
select datediff(second, '19700101', @dt) as TotalSeconds[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

tonyz
Starting Member

5 Posts

Posted - 2007-07-05 : 05:25:18

thats ok when defining one string.. but I have to troll through 100's of rows to do the comparison, I need something like :

declare @dt datetime
set @dt = (select value from ahd.prp where value != '')
select datediff(second, '19700101', @dt) as TotalSeconds

but this causes en error complaining about the select (guess u cant use it with a declare statement)

basically I have 2 columns, one contains a date that is a string the other has the date in seconds since 1/1/1970

how do I write the query comparing all values in these two columns?

thnx
tony

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources