SQL one day before ( Yesterday ) from Current date

Below syntax allows me to get a day prior

SELECT dateadd(day,datediff(day,1,GETDATE()),0)

Open in new window


When I implement in my where condition for my Table - Date

It doesn't return exact date.

This is how my actual date in the database looks like

2015-03-19 00:00:00.000

I need to implement in the where condition
chokkaStudentAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
These set the time to 00:00:00 (beginning of the day)

dateadd(day, datediff(day,0, GETDATE() ), 0)  

CAST(GETDATE() AS DATE)

Yesterday therefore is one day less than this:

DATEADD(day,1,   dateadd(day, datediff(day,0, GETDATE() ), 0)   )

DATEADD(day,1,  CAST(GETDATE() AS DATE) )

{+ edit:}
If you need a datetime value returned then don't use CAST(... AS DATE)
0
 
PortletPaulfreelancerCommented:
This is how my actual date in the database looks like

2015-03-19 00:00:00.000

Please note that is simply does NOT matter what a date/time value "looks like". Internally the database is actually storing that information as integers. What you see is a "style" (or "format") that as the default output for that type of data.

In other words, you can alter the presentation style of a date to 19/03/2015 or 03/19/2015 but they are all the same date

for more see:
The ultimate guide to the datetime datatypes
http://www.experts-exchange.com/articles/12315/SQL-Server-Date-Styles-formats-using-CONVERT.html
0
 
chokkaStudentAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.