Date Question - 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 2008 Forums

Transact-SQL (2008)

Date Question

Author

Topic

ZMike
Posting Yak Master

110 Posts

Posted - 2010-04-01 : 13:38:11

I have an unusual request from a user.. My Month ends run from the 19 - 18 every month.

For instance today is 4/1/2010.

From 3/19 - 4-18 = April

How would I put in either TSQL or a Stored Procedure the following

I always want to default to the current Month but when I push this out to reporting services be able to make it a selectable drop down

So Default to April
-1 = March (2/19/2010 - 3/18/2010)
-2 = Feb (1/19/2010 - 2/18/2010)

Etc... I'd like to leave it -1 then a month name. I need to basically keep 2 years in there for a selection.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 13:45:01

[code]SELECT -Val,DATEADD(mm,DATEDIFF(mm,0,GETDATE())-Val-1,0)+18 AS Start,DATEADD(mm,DATEDIFF(mm,0,GETDATE())-Val,0)+17 AS [End]
FROM (SELECT 0 AS VAL UNION ALL
SELECT 1 UNION ALL
SELECT 2)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ZMike
Posting Yak Master

110 Posts

Posted - 2010-04-01 : 14:05:48

visakh16,

As always you're quick and correct... That's what I'm looking for.

Without messing up your logic I added the extra months and changed the names a bit.... I want to use the exact data as is and add one extra colum that takes the month and year

0 = Current Month ---- New Column April 2010
-1 = March (2/19/2010 - 3/18/2010) March 2010
-2 = Feb (1/19/2010 - 2/18/2010) February 2010

Etc... .Here is the additions I have so far.

SELECT -Val AS [Month Number],DATEADD(mm,DATEDIFF(mm,0,GETDATE())-Val-1,0)+18 AS [Month Start],DATEADD(mm,DATEDIFF(mm,0,GETDATE())-Val,0)+17 AS [Month End]
FROM (SELECT 0 AS VAL
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9
UNION ALL
SELECT 10
UNION ALL
SELECT 11
UNION ALL
SELECT 12
UNION ALL
SELECT 13
UNION ALL
SELECT 14
UNION ALL
SELECT 15
UNION ALL
SELECT 16
UNION ALL
SELECT 17
UNION ALL
SELECT 18
UNION ALL
SELECT 19
UNION ALL
SELECT 20
UNION ALL
SELECT 21
UNION ALL
SELECT 22
UNION ALL
SELECT 23
UNION ALL
SELECT 24 )T

quote:
Originally posted by visakh16

SELECT -Val,DATEADD(mm,DATEDIFF(mm,0,GETDATE())-Val-1,0)+18 AS Start,DATEADD(mm,DATEDIFF(mm,0,GETDATE())-Val,0)+17 AS [End]
FROM (SELECT 0 AS VAL UNION ALL
SELECT 1 UNION ALL
SELECT 2)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 14:09:26

[code]
SELECT [Month Number],[Month Start],[Month End],DATENAME(mm,[Month End]) + ' ' + DATENAME(yy,[Month End])
FROM
(
SELECT -Val AS [Month Number],DATEADD(mm,DATEDIFF(mm,0,GETDATE())-Val-1,0)+18 AS [Month Start],DATEADD(mm,DATEDIFF(mm,0,GETDATE())-Val,0)+17 AS [Month End]
FROM (SELECT 0 AS VAL
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9
UNION ALL
SELECT 10
UNION ALL
SELECT 11
UNION ALL
SELECT 12
UNION ALL
SELECT 13
UNION ALL
SELECT 14
UNION ALL
SELECT 15
UNION ALL
SELECT 16
UNION ALL
SELECT 17
UNION ALL
SELECT 18
UNION ALL
SELECT 19
UNION ALL
SELECT 20
UNION ALL
SELECT 21
UNION ALL
SELECT 22
UNION ALL
SELECT 23
UNION ALL
SELECT 24 )T
)r
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ZMike
Posting Yak Master

110 Posts

Posted - 2010-04-01 : 15:10:13

Woohoo... That's it. As far as I know this is absolutely perfect

SELECT [Month Number],
DATENAME(mm,[Month End Date]) + ' ' + DATENAME(yy,[Month End Date]) AS [Month End],
[Month Start Date],
[Month End Date],
CAST([Month Number] AS VARCHAR) + ' ' + CAST(DATENAME(mm,[Month End Date]) + ' ' + DATENAME(yy,[Month End Date]) AS VARCHAR) AS [Month # Year]

FROM
(
SELECT -Val AS [Month Number],DATEADD(mm,DATEDIFF(mm,0,GETDATE())-Val-1,0)+18 AS [Month Start Date],DATEADD(mm,DATEDIFF(mm,0,GETDATE())-Val,0)+17 AS [Month End Date]
FROM (SELECT 0 AS VAL
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5
UNION ALL
SELECT 6
UNION ALL
SELECT 7
UNION ALL
SELECT 8
UNION ALL
SELECT 9
UNION ALL
SELECT 10
UNION ALL
SELECT 11
UNION ALL
SELECT 12
UNION ALL
SELECT 13
UNION ALL
SELECT 14
UNION ALL
SELECT 15
UNION ALL
SELECT 16
UNION ALL
SELECT 17
UNION ALL
SELECT 18
UNION ALL
SELECT 19
UNION ALL
SELECT 20
UNION ALL
SELECT 21
UNION ALL
SELECT 22
UNION ALL
SELECT 23
UNION ALL
SELECT 24 )T
)r

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 15:13:04

cheers

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ZMike
Posting Yak Master

110 Posts

Posted - 2010-04-05 : 15:04:16

visakh16,

4 more questions. I didnt think to ask before.

Is how do I make all field with every day in the month that I can join to

and also a field for the day of the month... (Since the dates run from the 19-18) then 19th would be Day 1, 20th = Day 2 etc.

Also I want to add Quarter in there. for instance

12/19 - 3/18 = 1
3/19 - 6/18 = 2
6/19 - 9/18 = 3
9/19 - 12/18 = 4

Then Maybe having Quarter / Year ?

Sorry for the extras.... They just sounded really useful and I'm sure some of the others could use this as well (may just have to change the dates slightly.)

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-05 : 20:49:14

This data only changes once a month, so rather than running a query full of hard coded values every time you need this, you should store the values in a table, and used a SQL Server agent job to update the table at the start of each month. This way, the server isn't repeatedly making the same calculations every time a report is run. It will be much more efficient. It sounds like something you'll use quite often.

Also, it's not a good idea to store data in code. For example, the day of the month on which your month starts/ends is data, so it should be stored in a table.

There are 10 types of people in the world, those that understand binary, and those that don't.

ZMike
Posting Yak Master

110 Posts

Posted - 2010-04-05 : 21:21:52

DBA,

That's actually what I'm doing. I'm going to store it to a table and have it updated daily once I get the couple extra additions added.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-06 : 01:38:07

1. use a table as suggested by DBA
2. Add a count column using ROW_NUMBER function for getting day number
3. How will you determine the start and end of each quarter? or do you mean actual calendar quarter?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-04-06 : 05:56:24

quote:
Originally posted by visakh16

2. Add a count column using ROW_NUMBER function for getting day number

You might as well store it directly in the table.

quote:
3. How will you determine the start and end of each quarter? or do you mean actual calendar quarter?

I'd use a separate table for that.

There are 10 types of people in the world, those that understand binary, and those that don't.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources