Date lying between 2 days - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date lying between 2 days

Author

Topic

EASYSQL
Starting Member

10 Posts

Posted - 2006-04-03 : 14:36:47

In the following query i want to get the orders for previous day and not the current day.
I am unable to frame the date range for this, so i am leaving it blank.I tried so many ways but what i need is date starting from zero hrs yesterday till zero hrs today.I need report lying for the day before today.
Thanks

I have the following query

SELECT O.work_order_id,O.LOAN_NUMBER,WEB_SUBMIT_DT
FROM OA_EST_HDR A (NOLOCK)
JOIN grasscutordercompletion B (NOLOCK) ON A.ORDER_ID = B.grasscutorderID
WHERE b.SUBMIT_STATUS = 0 AND O.ORDER_STATUS = 13
and day(WEB_SUBMIT_DT) =

I have the date in the following format "2004-01-28 08:49:00"

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-04-03 : 16:47:30

Have a look at
-- dateadd
-- datediff
functions.

U r too close to the answer, so better u try urself.

Srinika

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-03 : 19:33:39

[code]
select dateadd(day, datediff(day, 0, getdate()), -1) as [0 hour yesterday],
dateadd(day, datediff(day, 0, getdate()), 0) as [0 hour today]
[/code]

KH
Choice is an illusion, created between those with power, and those without.

EASYSQL
Starting Member

10 Posts

Posted - 2006-04-03 : 21:47:43

Thanks it worked great but when i use getdate() -1 and getdate() 0 its populating the dates
2006-04-02 00:00:00.000
2006-04-02 00:00:00.000
2006-04-03 00:00:00.000
2006-04-03 00:00:00.000

but when i just want for 1 past day i am using
ord_date BETWEEN dateadd(day, datediff(day, 0, getdate()), -1) and dateadd(day, datediff(day, 0, getdate()), -1)
so it gives me the date range,hope this is correct and it is what i wanted.
2006-04-02 00:00:00.000
2006-04-02 00:00:00.000
2006-04-02 00:00:00.000
2006-04-02 00:00:00.000

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-04 : 02:49:21

If you want to select only yesterday's order

where ord_date >= dateadd(day, datediff(day, 0, getdate()), -1)
and   ord_date <  dateadd(day, datediff(day, 0, getdate()), 0)

Note : BETWEEN is inclusive of both date
From Books Online on BETWEEN

quote:
BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.

KH
Choice is an illusion, created between those with power, and those without.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources