0

I have a date column like this 7/24/2017 and when I write the below where clause I get results from hour 7/24/2017 1:00:00.000 AM. I need to get this from 7/24/2017 12:00:00.000 AM. How should this where clause me modified. Please check the following code

Date>= DATEADD(day, -1, convert(date, GETDATE())) and 
Date< DATEADD(day, +0,  convert(date, GETDATE()))
Sunny Babu
  • 9
  • 5
  • 2
    Might this be a daylight saving problem? I always use GETUTCDATE() to avoid ambiguity. `print cast(GetUTCDate() as varchar(100)) print cast(GetDate() as varchar(100)) ` results in `Jul 25 2017 8:42AM Jul 25 2017 9:42AM` – JohnRC Jul 25 '17 at 08:41

1 Answers1

0

There doesn't appear to be anything wrong with your code. Are you certain your data contains any times before 1am? Are you certain you appreciate how your SQLServer will format those times/represent them to you?

See this SQLFIDDLE: http://sqlfiddle.com/#!6/047cc/11

Optimisations: you don't need to dateadd a value of 0. You can also subtract 1.0 from a date to get the day earlier and it's a bit less wordy than DATEADD. It works because internally dates are represented as floating point numbers of the number of days since a point in time:

[Date] >= convert(date, GETDATE() -1.0) and 
[Date] < convert(date, GETDATE())
Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • Yes, but getdate() returns a datetime, not a date. Add/sub work with datetime... You'll note I put my 1.0 inside the brackets so it is applied to getdate() not the conversion of the datetime to date. – Caius Jard Jul 25 '17 at 08:46
  • Generally, you would cast correctly, It's most efficient. Over millions of rows, cast works and every other technique (varchar, float, etc) loses. This https://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server/1177529#1177529 – gbn Jul 25 '17 at 09:21
  • I still get the hour starting at 1:00 am, for the previous day, as 7/24/2017 1:00:00.000 AM. Not sure why it is not showing 12:00 am for previous day, however the last record as 7/25/2017 12:00:00.000 AM. – Sunny Babu Jul 25 '17 at 09:23
  • Caius Jard , I like your optimize query.. makes life much easier. I solved my issue. thanks – Sunny Babu Jul 25 '17 at 09:49
  • what is the optimized syntax to get previous year? similar to [Date] >= convert(date, GETDATE() -1.0) and [Date] < convert(date, GETDATE()) – Sunny Babu Jul 25 '17 at 16:57
  • Probably safest to use `DATEADD(year, -1, getdate())` because of leap years, need something more intelligent than simply subtracting 365.. – Caius Jard Jul 25 '17 at 19:19