SQL Query records for yesterdays date

[jwb]

Would anyone have a way to query SQL server data for yesterday's date? I am trying to query a datetime field and use this in a grid component but having trouble with datetime field.

[workaholic06]

For SQL, there's a dateadd() function you can use.

so your query could be:

Select * from tablename where dateadd(day,-1,datecolumn) = curdate()


Here's a link to the function:
http://www.w3schools.com/sql/func_dateadd.asp

Thanks,

Robert

[jwb]

I probably didn't explain correctly. I need to find records where the datetime field contains only yesterdays date.

[workaholic06]

Ok, try:

Select * from tablename where dateadd(day,-1,curdate()) = datecolumn

Thanks,

Robert

[workaholic06]

Sorry - that was wrong I didn't realize you had a 'datetime' field.. I read date.

Try this:

Select datecolumn, DATEADD(D, 0, DATEDIFF(D, 0, datecolumn)) as dateonly_datecolumn, DATEADD(D, 0, DATEDIFF(D, 1233, GETDATE())) as yesterday from tablename
WHERE DATEADD(D, 0, DATEDIFF(D, 0, creation_date)) = DATEADD(D, 0, DATEDIFF(D, 1, GETDATE()))

Try it without the WHERE first, and you'll see what it does.

Thanks,

Robert

[chadbrown]

this is a where statement I use

now() - interval 2 day

[glenschild]

getdate() returns a datetime value so how about

SELECT * FROM table WHERE CAST(datecolumn AS DATE) = DATEADD(dd,-1,CAST(getdate() as DATE))

[madtowng]

Hi Jeff,
Try the code below.
You will need to replace <TABLE> with the name of your table, and <COLUMN> with the name of your datetime column.

More graceful solutions may exist, but this should do what you want.

select * from <TABLE>
where left(convert(varchar,dateadd(d,-0,<COLUMN>),120),10) =left(convert(varchar,dateadd(d,-1,getdate()),120),10)

[madtowng]

Hi Jeff,
Try the code below.
You will need to replace <TABLE> with the name of your table, and <COLUMN> with the name of your datetime column.

More graceful solutions may exist, but this should do what you want.

select * from <TABLE>
where left(convert(varchar,dateadd(d,-0,<COLUMN>),120),10) =left(convert(varchar,dateadd(d,-1,getdate()),120),10)