DatePart Calculations - 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

SQL Server 2000 Forums

Transact-SQL (2000)

DatePart Calculations

Author

Topic

ninel
Posting Yak Master

141 Posts

Posted - 2008-02-28 : 15:16:46

I have to create a report that runs every Monday, but pulls data from the previous Sunday to Saturday.

For example: The report ran on Feb 25th, but pulled data from Feb 17th(Sun) to Feb 23rd(Sat).

This is what I have so far, but can't figure out what I need:


SELECT * 
FROM table1 
WHERE date >=  convert(datetime,convert(varchar(30),getdate() - DATEPART(dw,getdate()), 112) +2)

Can anyone help me with the correct query?

Thanks,
Ninel

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 16:09:56

[code]DECLARE @FromDate DATETIME,
@ToDate DATETIME

SELECT @FromDate = DATEADD(DAY, DATEDIFF(DAY, '19000106', GETDATE()) / 7 * 7, '18991231'),
@ToDate = DATEADD(DAY, 7, @FromDate)

SELECT *
FROM Table1
WHERE Col1 >= @FromDate
AND Col1 < @ToDate[/code]

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-02-28 : 17:26:07

quote:
Originally posted by Peso

...
SELECT @FromDate = DATEADD(DAY, DATEDIFF(DAY, '19000106', GETDATE()) / 7 * 7, '18991231'),
@ToDate = DATEADD(DAY, 7, @FromDate)
...

E 12°55'05.25"
N 56°04'39.16"

That select where you assign @FromDate and @ToDate makes me nervous.

Is it documented that it will work that way, or is that just something that happens to work?

I would do it this way:


SELECT	@FromDate = DATEADD(DAY, DATEDIFF(DAY, '19000106', GETDATE()) / 7 * 7, '18991231')
SELECT	@ToDate = DATEADD(DAY, 7, @FromDate)

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-28 : 17:34:25

It has never failed me.

Often I do this when concatenating long string

SELECT	@SQL = 'fksdn sdfk df nsd ',
		@SQL = @SQL + QUOTENAME(@werww),
		@SQL = @SQL + ' FROM xyz'

It would be very nervous if the query engine decides to not start from top.
But I have never encountered this. For me, the query engine has always started from top, just like having many WHEN in a CASE statement.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-29 : 06:04:04

quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by Peso

...
SELECT @FromDate = DATEADD(DAY, DATEDIFF(DAY, '19000106', GETDATE()) / 7 * 7, '18991231'),
@ToDate = DATEADD(DAY, 7, @FromDate)
...

E 12°55'05.25"
N 56°04'39.16"

That select where you assign @FromDate and @ToDate makes me nervous.

Is it documented that it will work that way, or is that just something that happens to work?

I would do it this way:

SELECT @FromDate = DATEADD(DAY, DATEDIFF(DAY, '19000106', GETDATE()) / 7 * 7, '18991231')
SELECT @ToDate = DATEADD(DAY, 7, @FromDate)

CODO ERGO SUM

Yes. To be on safer side I always use as you specified

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources