Current Date - 1

Question

Current Date - 1

aberndt

SSCrazy

Points: 2003
More actions
June 29, 2010 at 10:21 am

#224342

I am trying to write a Crystal report where I am pulling records from yesterday. The report will be run daily, hopefully automatically, so there is no need to enter parameters. The problem I am having is comparing a datetime field (shift_date) to DATEADD(d, -1, getdate()). If I use
"where SHIFT_DATE = DATEADD(d, -1, getdate())", I get no data as I suppose finding an exact match is impossible so that makes sense. If I use "where SHIFT_DATE > DATEADD(d, -1, getdate())", I only get todays data and if I use < I get everything that ever happened. I only want data from the previous day. This seems like it should be an easy answer but it isn't working or I'm missing something. Thank you in advance for your help>

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 1

aberndt (6/29/2010)
I am trying to write a Crystal report where I am pulling records from yesterday. The report will be run daily, hopefully automatically, so there is no need to enter parameters. The problem I am having is comparing a datetime field (shift_date) to DATEADD(d, -1, getdate()). If I use
"where SHIFT_DATE = DATEADD(d, -1, getdate())", I get no data as I suppose finding an exact match is impossible so that makes sense. If I use "where SHIFT_DATE > DATEADD(d, -1, getdate())", I only get todays data and if I use < I get everything that ever happened. I only want data from the previous day. This seems like it should be an easy answer but it isn't working or I'm missing something. Thank you in advance for your help>

Try this :

WHERE SHIFT_DATE >= DATEADD(d,-1,CONVERT(DATETIME,CONVERT(varchar(11),GETDATE())))

Converting oxygen into carbon dioxide, since 1955.

aberndt SSCrazy Points: 2003 More actions · Answer 2

Steve - Thank you, thank you, thank you!!! I removed the > from your statement as I need only yesterday's data, not today's and it worked great!!! I appreciate your expertise and your prompt response!!!

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 3

You are most welcome.

Thank you for the feedback.

Converting oxygen into carbon dioxide, since 1955.

WayneS SSC Guru Points: 95460 More actions · Answer 4

aberndt (6/29/2010)
Steve - Thank you, thank you, thank you!!! I removed the > from your statement as I need only yesterday's data, not today's and it worked great!!! I appreciate your expertise and your prompt response!!!

Umm... removing the > means that you are only matching to a date with a time of midnight... is the time portion of all the dates in the SHIFT_DATE column removed? If not, it's not going to work correctly!

Also, this method of converting date -> string -> date is not quite as efficient as using the date manipulation functions.

To handle both of these situations, you would want to:

WHERE SHIFT_DATE >= DateAdd(d, DateDiff(d, 0, GetDate())-1, 0)

AND SHIFT_DATE < DateAdd(d, DateDiff(d, 0, GetDate()),0)

For other great, efficient date manipulations, see the "Common Date/Time Routines" link in my signature.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

aberndt SSCrazy Points: 2003 More actions · Answer 5

Wayne - Your suggestion works great too. Both return 481 records and they both generate the desired results of a report that will show me all active employees and their clock in and out times including all employees that didn't clock in or out (it will ultimately be an exception report) For some reason in our db, the data in the shift_date column doesn't really use the 'time' portion, it shows as 2008-11-25 00:00:00.000 (example). Thank you for the reference to the article regarding common date/time routines. Many of the reports I write use date/time functions so I can see myself referring back to that quite frequently. Thank you so much for your help and expertise!!!

WayneS SSC Guru Points: 95460 More actions · Answer 6

aberndt (6/29/2010)
the data in the shift_date column doesn't really use the 'time' portion, it shows as 2008-11-25 00:00:00.000 (example).

This explains why just the = works.

Thank you for the reference to the article regarding common date/time routines. Many of the reports I write use date/time functions so I can see myself referring back to that quite frequently. Thank you so much for your help and expertise!!!

NP, and glad to be of help. And thanks for posting back with the results of your testing!

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2