Setting Date Variables - 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

Setting Date Variables

Author

Topic

jrobin747
Starting Member

48 Posts

Posted - 2013-08-26 : 10:16:33

I have theses variables
@strStartDaysDate VARCHAR(10),
@strStartWeekDate VARCHAR(10),
@strStartMonthDate VARCHAR(10),
@strEndDate VARCHAR(10),

SET @strStartDaysDate='08/01/2013'
SET @strStartWeekDate='08/01/2013'
SET @strStartMonthDate='08/01/2013'
SET @strEndDate=GETDATE ()

I'm doing counts in the select and
In my where clause I'm doing like this
WHERE MerApp.LastUpdateOn BETWEEN @strStartDayDate AND @strEndDate
WHERE MerApp.LastUpdateOn BETWEEN @strStartWeekDate AND @strEndDate
WHERE MerApp.LastUpdateOn BETWEEN @strStartMonthDate AND @strEndDate

So with @strStartDaysDate I'm getting number of records for the day.

With @strStartWeekDate I'm getting number of records for the week.

With @strStartMonthDate I'm getting number of records for the month.

I'm using GETDATE () function for @strEndDate because I can't count records after the current date.

Of course those physical dates ('08/01/2013') are not good because I would have to have new dates each week or each month.

How do I properly set my variables once so that they regardless to the month or year? I hope that makes sense.
@strStartDaysDate
@strStartWeekDate
strStartMonthDate

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-26 : 10:25:43

[code]SET @strStartDaysDate=DATEADD(day,DATEDIFF(day,0,GETDATE()),0)
SET @strStartWeekDate=DATEADD(week,DATEDIFF(week,0,GETDATE()),0) --- ?
SET @strStartMonthDate=DATEADD(month,DATEDIFF(month,0,GETDATE()),0)
[/code]This assumes Monday as the start of the week. If you want to use a different day as start of the week, then change that last 0 (to -1 for Sunday, for example).

But, is what you want to do something different? (Count the first seven days of the month as the first week etc.?)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-26 : 11:24:07

Try to avoid using WEEK for calulating anything with weeks in SQL Server, because it's depending on SET DATEFIRST setting.
See this repro

WITH cteDates(theDate)
AS (
	SELECT	'20130819' UNION ALL
	SELECT	'20130820' UNION ALL
	SELECT	'20130821' UNION ALL
	SELECT	'20130822' UNION ALL
	SELECT	'20130823' UNION ALL
	SELECT	'20130824' UNION ALL
	SELECT	'20130825'
)
SELECT	DATEADD(WEEK, DATEDIFF(WEEK, 0, theDate), 0)
FROM	cteDates;

It gets even funnier when using other values for SET DATEFIRST than the default 7.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

jrobin747
Starting Member

48 Posts

Posted - 2013-08-26 : 11:58:00

Thanks James

How about this
SET @strStartofMonth=(@strCurrentMonth + '/01/' + @strCurrentYear)
SET @strCurrentMonth=MONTH (GETDATE ())
SET @strCurrentYear=YEAR (GETDATE ())

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-26 : 12:35:31

quote:
Originally posted by jrobin747

Thanks James

How about this
SET @strStartofMonth=(@strCurrentMonth + '/01/' + @strCurrentYear)
SET @strCurrentMonth=MONTH (GETDATE ())
SET @strCurrentYear=YEAR (GETDATE ())

It will work (you need to swap the order of the statements, of course), but I prefer the way that I had posted.

Things I don't like about the way you are doing it is that you are converting the dates to characters and then composing; it may not work for all locale settings and language settings etc.

As a general rule, it is a good idea to keep dates and times as date/datetime data types rather than convert them to character types.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-26 : 12:37:31

quote:
Originally posted by SwePeso

Try to avoid using WEEK for calulating anything with weeks in SQL Server, because it's depending on SET DATEFIRST setting.
See this repro
WITH cteDates(theDate)
AS (
SELECT '20130819' UNION ALL
SELECT '20130820' UNION ALL
SELECT '20130821' UNION ALL
SELECT '20130822' UNION ALL
SELECT '20130823' UNION ALL
SELECT '20130824' UNION ALL
SELECT '20130825'
)
SELECT DATEADD(WEEK, DATEDIFF(WEEK, 0, theDate), 0)
FROM cteDates;
It gets even funnier when using other values for SET DATEFIRST than the default 7.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Indeed Swepeso! Thanks for pointing it out.

Jrobin747, the question I asked in my first reply - how do you define your week? Does it start on the first of the month and count seven days till next week, or does it follow some other weekday based rule?

jrobin747
Starting Member

48 Posts

Posted - 2013-08-26 : 13:21:57

Yes the week starts on Monday. Sorry for not answering the question.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-26 : 14:32:13

quote:
Originally posted by jrobin747

Yes the week starts on Monday. Sorry for not answering the question.

You can use this:

DATEADD(dd,-DATEDIFF(dd,0,GETDATE())%7,CAST(GETDATE() AS DATE));

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-26 : 15:11:43

DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) / 7 * 7, 0)

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources