Between Date/Time Data - 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

General SQL Server Forums

New to SQL Server Programming

Between Date/Time Data

Author

Topic

dzjc
Starting Member

2 Posts

Posted - 2011-05-27 : 17:17:35

I have a table that stores the date/time data in the following format: 1304398443.

I can get the date/time using:

SELECT DateAdd(ss, LastOccurTime-3600, '1969-12-31T18:00:00.000') as LastOccurTime

However, when I try and select data between specific *times*, I get no data (even though the records are there). And here's what I've tried:

CAST(FLOOR(CAST(DATEADD(ss,lastoccurtime, '1969-12-31T18:00:00.000') AS FLOAT)) AS DATETIME) BETWEEN '2011-5-2 20:00:00.000' and '2011-5-2 23:00:00.000'

I'm no expert on this date/time conversion stuff, so any help is greatly appreciated!

Thanks!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-05-27 : 17:40:15

If I understand your requirements, here are a couple ways:

-- Sample Data
DECLARE @Foo TABLE (LastOccurTime INT)
INSERT @Foo SELECT 1304398443


-- Methiod 1
SELECT 
    *, 
    DATEADD(SECOND, (LastOccurTime-3600), '1969-12-31T18:00:00.000') as LastOccurTime
FROM 
    @Foo
WHERE 
    DATEADD(SECOND, (LastOccurTime-3600), '1969-12-31T18:00:00.000') 
        BETWEEN '2011-5-2 20:00:00.000' AND '2011-5-2 23:00:00.000'

-- Method 2 -- better
SELECT 
    *, 
    DATEADD(ss, (LastOccurTime-3600), '1969-12-31T18:00:00.000') as LastOccurTime
FROM 
    @Foo
WHERE
    LastOccurTime BETWEEN DATEDIFF(SECOND, '1969-12-31T18:00:00.000', '2011-5-2 20:00:00.000') + 3600
    AND DATEDIFF(SECOND, '1969-12-31T18:00:00.000', '2011-5-2 23:00:00.000') + 3600

dzjc
Starting Member

2 Posts

Posted - 2011-06-01 : 09:28:15

Thanks. Worked great!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-01 : 11:18:15

If you want to use an index, try this

-- Peso method (sargable, can utilize an index over LastOccurTime column)
SELECT	*, 
	DATEADD(SECOND, LastOccurTime, '19691231 17:00:00') as LastOccurTime
FROM	@Foo
WHERE	LastOccurTime >= 3600 * DATEDIFF(HOUR, '19691231 17:00:00', '20110502 20:00:00')
	AND LastOccurTime <= 3600 * DATEDIFF(HOUR, '19691231 17:00:00', '20110502 23:00:00')

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources