SQL Syntax

HKFuey
HKFuey used Ask the Experts™
on
I'm trying to understand someone else's syntax: -

These are the parameters: -
      DECLARE @TodayStart AS DATETIME, @TodayEnd AS DATETIME
      SET @TodayStart = DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
      SET @TodayEnd = DATEADD(dd, 1, @TodayStart)

This is the 'where' part of the query: -
      WHERE [ReceiptDate] >= '''+@TodayStart+'''
      AND [ReceiptDate] < '''+@TodayEnd+'''

Can anyone tell me what date range will be returned by this query?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Geert GOracle dba
Top Expert 2009

Commented:
lol ... i guess this are the same 2 lines
SET @TodayStart = DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

SET @TodayStart = getdate()


daterange is from today until tomorrow

Author

Commented:
So, even though it is convoluted, the results will be transactions from 'Today'?
Project Leader
Commented:
Hello,

SET @TodayStart = DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

This statement sets @TodayStart as beginning time of today, like '2014/03/06 00:00:00'
Below statement adds one day to @TodayStart which will set @TodayEnd to beginning time of tomorrow, like '0314/03/07 00:00:00'
SET @TodayEnd = DATEADD(dd, 1, @TodayStart)

And below final clause will return all records with ReceiptDate falling any time today. This is required only when you have time part in ReceiptDate column:
WHERE [ReceiptDate] >= '''+@TodayStart+'''
      AND [ReceiptDate] < '''+@TodayEnd+'''


-Harish

Author

Commented:
That's very clear, thanks very much!
Harish VargheseProject Leader

Commented:
You can also replace   SET @TodayStart = DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
with   SET @TodayStart = convert(varchar, getdate(), 111), which is just trimming the time part from datetime value.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial