CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008
SiriusBits
SiriusBits

Reputation: 800

How can I get current date + 4 HOURS in a SQL Statement?

I'm looking to retrieve data from a a table, between YESTERDAY (starting at 4am) and TODAY (ending at 4am). What is the best way to go about automating this? I am thinking perhaps grab today's date and offsetting 4 hours and assign it to a variable? Currently manually entering the time/date each day.

Current code:

SELECT "HC_PRIME_REPORTDATA"."iReportDataID"
FROM   "WinTest1"."dbo"."HC_PRIME_REPORTDATA" "HC_PRIME_REPORTDATA"
WHERE  ("HC_PRIME_REPORTDATA"."dtTime">={ts '2011-10-19 04:00:00'} AND"HC_PRIME_REPORTDATA"."dtTime"<{ts '2011-10-20 04:00:00'})

Any help is greatly appreciated!

Upvotes: 2

Views: 5498

Answers (2)

Factor Mystic
Factor Mystic

Reputation: 26800

dateadd will do what your title asks:

For example: select getdate(), dateadd(hh, 4, getdate())

However, you can filter by date range using between, no need for variables:

For example:

SELECT  HC_PRIME_REPORTDATA.iReportDataID
FROM    WinTest1..HC_PRIME_REPORTDATA HC_PRIME_REPORTDATA
WHERE   HC_PRIME_REPORTDATA.dtTime between dateadd(hh, 4, convert(datetime, convert(date, dateadd(d, -1, getdate()))))
                                            and dateadd(hh, 4, convert(datetime, convert(date, getdate())))

Upvotes: 2

Michael Goldshteyn
Michael Goldshteyn

Reputation: 74470

Here is one way to do it:

DECLARE @HOURS_OFFSET INT=4; -- 4 AM

DECLARE @YESTERDAY_WITH_OFFSET DATETIME=DATEADD(hh, @HOURS_OFFSET, DATEADD(dd, -1, 
                                  DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)));
DECLARE @TODAY_WITH_OFFSET DATETIME=DATEADD(hh, @HOURS_OFFSET,DATEADD(dd, 
                                      DATEDIFF(dd, 0, GETDATE()), 0));

SELECT HC_PRIME_REPORTDATA.iReportDataID 
FROM   WinTest1.dbo.HC_PRIME_REPORTDATA AS HC_PRIME_REPORTDATA 
WHERE  HC_PRIME_REPORTDATA.dtTime >= @YESTERDAY_WITH_OFFSET 
   AND HC_PRIME_REPORTDATA.dtTime <= @TODAY_WITH_OFFSET

Upvotes: 1

Related Questions