Getting Dates - today, yesterday help

01-04-07, 04:18 PM

trint

Our order dates field looks like this:
2007-01-01 11:11:43.000
If I just wanted to do a Select on orders today or
between a date range, is there a simple way?
Instead of like:

Select *
>From orders
Where order_date Like '%2007-01-04%'

Thanks,
Trint

01-04-07, 04:25 PM

Tom Cooper

Best way is

Select *
From orders
Where order_date >= '20070104' And order_date < '20070105'

Tom

"trint" <trinity.smith> wrote in message
news:5300
[..]

01-04-07, 04:28 PM

Tracy McKibben

trint wrote:
> Our order dates field looks like this:
> 2007-01-01 11:11:43.000
> If I just wanted to do a Select on orders today or
> between a date range, is there a simple way?
> Instead of like:
>
> Select *
>>From orders
> Where order_date Like '%2007-01-04%'
>
> Thanks,
> Trint
>

This will return orders since midnight last night (20070101 00:00:00.000).

SELECT *
FROM Orders
WHERE Order_Date >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

01-04-07, 04:31 PM

Mike C#

Something like this? (*untested*)

DECLARE @today DATETIME

SELECT @today = DATEDIFF(day, 0, GETDATE())

DECLARE @yesterday DATETIME

SELECT @yesterday = DATEADD(day, -1, @today)

DECLARE @tomorrow DATETIME

SELECT @tomorrow = DATEADD(day, 1, @today)

SELECT @yesterday, @today, @tomorrow

-- Get today's orders
SELECT *
FROM Orders
WHERE order_date >= @today AND @order_date < @tomorrow

-- Get yesterday's orders
SELECT *
FROM Orders
WHERE order_date >= @yesterday AND order_date < @today

"trint" <trinity.smith> wrote in message
news:5300
[..]

01-04-07, 05:20 PM

Aaron Bertrand [SQL Server MVP]

Never use LIKE for datetime comparisons. Datetime values are not even
stored as strings!

-- arbitrary date:
DECLARE @dt SMALLDATETIME;
SET @dt = '20070101';
SELECT column_list FROM Orders
WHERE Order_date >= @dt
AND Order_date < (@dt + 1);
GO

-- today (assuming no future orders):
DECLARE @dt SMALLDATETIME;
SET @dt = DATEDIFF(DAY, 0, CURRENT_TIMESTAMP);
SELECT column_list FROM Orders
WHERE Order_date >= @dt;
GO

-- yesterday:
DECLARE @dt SMALLDATETIME;
SET @dt = DATEDIFF(DAY, 0, CURRENT_TIMESTAMP);
SELECT column_list FROM Orders
WHERE Order_Date >= (@dt - 1)
AND Order_date < @dt;
GO

-- date range, e.g. 2007-01-01 -> 2007-01-04:
DECLARE @sdt SMALLDATETIME, @edt SMALLDATETIME;
SET @sdt = '20070101';
SET @edt = '20070104';
SELECT column_list FROM Orders
WHERE Order_Date >= @sdt
AND Order_date < (@edt + 1);
GO

These queries will all use an index on order_date, if you have one (which
you should, if you are going to be running queries like this frequently).

"trint" <trinity.smith> wrote in message
news:5300
[..]

01-04-07, 05:31 PM

trint

All of these work greatly,
Thanks,
Trint

Aaron Bertrand [SQL Server MVP] wrote:
[..]

Similar Threads
Today and yesterday Can you tell me which the most simple method to get today´s date and yesterday´s date in solaris and red hat ?
yesterday, today, tomorrow user friendly dates, possible? Hi, Is it possible to display dates as yesterday, today or tomorrow instead of numeric dates in a list view? Thanks.
RDP-Yesterday Yes, Today No "The specified remote computer could not be found" when I attempt RDP connection to office PC. Office PC behind Netgear router, Windows Firewall deactivated, Norton Internet...
criteria for dates for yesterday and today Access 2K/Win2K. Field has date/time data type. Data is only date - no time included. Using the criteria: between date()-1 and date() but getting error: Data type...
Today and Yesterday TableA has a listing of several companies that are imported daily. How would I set the cirtieria of of tableA.date in a query to show me all the companies that have a date...