3

Ok, I'm running query builder out of visual studio 2008. I'm trying to filter the results of the query by time; that is, I want to pull everything from the start of yesterday to noon yesterday. I've been using GETDATE()-1 for yesterday, which pulls up a timestamp mm/dd/yyyy hh:mm:ss however, it pulls the current time. to get it to run from the start of the day I appended the timestamp to remove the time itself, so it started at the beginning of the day:

convert(varchar(10), getdate()-1, 120)

so I'm using between to find the range, I have: BETWEEN convert(varchar(10), getdate()-1, 120) AND // this is where I need it to cut off at noon.

I'm understanding that datetime is a data type here, so I tried subtracting the hours/minutes/seconds using date part, but datepart() only returns ints and doesn't affect the time.

thoughts? how do I get this to cut off at noon

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
nbpeth
  • 2,967
  • 4
  • 24
  • 34

5 Answers5

3

Try this:

--Variables
declare @now datetime = getdate(),
        @yesterday datetime

--Yesterday starting datetime
select @yesterday = convert(datetime, convert(date, dateadd(day,-1,@now)))

--Your query to filter between y'day start and y'day noon
--Note that between means inclusive boundary values. (or use >= and <=)
select * from yourTable
where dateCol between @yesteray and dateadd(hour,12,@yesterday)
Kaf
  • 33,101
  • 7
  • 58
  • 78
1
between DateAdd(day, -1, cast(getdate() as date)) and DateAdd(hour, -12, cast(getdate() as date))

Edit: As mentioned in the comments, you can't use hours with a date, you have to cast it back to a datetime, thus:

between DateAdd(day, -1, cast(getdate() as date)) and DateAdd(hour, -12, cast(cast(getdate() as date) as datetime))
Mathew Collins
  • 376
  • 3
  • 14
  • Good point, need to cast it back to a datetime before we take the hours away. `and DateAdd(hour, -12, cast(cast(GetDate() as date) as DateTime))` Downvote unnecessary since it was pretty much there. – Mathew Collins Oct 18 '13 at 20:37
1
SELECT * FROM T WHERE YourDate BETWEEN CAST(GETDATE()-1 As DATE) AND DATEADD(Hour, -12, CAST(CAST(GETDATE() As DATE) As DATETIME) )

Beware because BETWEEN will include lower and upper boundaries, so you can simply replace BETWEEN with x >= y and y < z if you don't want yesterday at 12:00 to be taken in account

jazzytomato
  • 6,994
  • 2
  • 31
  • 44
  • "error message: the datepart hour is not supported by date function dateadd for data type date" – nbpeth Oct 18 '13 at 17:36
1
DECLARE
    @Min DATETIME
  , @Max DATETIME

SELECT
    @Min = DATEADD(DAY, -1, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))
  , @Max = DATEADD(HOUR, 12, CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME))

SELECT *
FROM <Table> x
WHERE x.[Date] BETWEEN @Min AND @Max
jdl
  • 1,104
  • 8
  • 12
  • in query designer, the declare statement is not supported – nbpeth Oct 18 '13 at 17:39
  • I don't have the rep to answer, but it worked with slight modification to make it work in query designer. thank you guys! BETWEEN CAST(GETDATE() - 1 AS DATE) AND DATEADD(HOUR, 12, CAST(FLOOR(CAST(GETDATE() - 1 AS FLOAT)) AS DATETIME)) – nbpeth Oct 18 '13 at 17:57
  • @user2716330 If it worked, could you accept and un-downvote my answer? – jdl Oct 18 '13 at 22:58
1

If you want to get the results from last 30 mins you need to use this. You can change MINUTE to HOUR too.

--Get now, hour and second included    
DECLARE @NOW DATETIME = GETDATE() 

--Get 30 mins from now
DECLARE @TranDate DATETIME 
SET @TranDate = CONVERT(DATETIME, CONVERT(DATETIME, DATEADD(MINUTE,-30,@NOW)))

After That, add below code to your where statement,

AND TK.TransactionDate >= @TranDate
Sabri Meviş
  • 2,231
  • 1
  • 32
  • 38