Query on Date - 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

Query on Date

Author

Topic

zhel04
Starting Member

38 Posts

Posted - 2011-09-13 : 00:23:48

How will i do a script to get the cut-off per month for example Month of September I want to get the two cut-off September 1-15 and Sept 16-30 this will happen every month.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-13 : 00:31:53

[code]
declare @year int = 2011,
@month int = 9

select cut_off_1_start,
cut_off_1_end = dateadd(day, day(cut_off_2_end)/2 - 1, cut_off_1_start),
cut_off_2_start = dateadd(day, day(cut_off_2_end)/2, cut_off_1_start),
cut_off_2_end
from
(
select cut_off_1_start = dateadd(month, (@year - 1900) * 12 + @month - 1, 0),
cut_off_2_end = dateadd(month, (@year - 1900) * 12 + @month, -1)
) d
[/code]

KH
[spoiler]Time is always against us[/spoiler]

zhel04
Starting Member

38 Posts

Posted - 2011-09-13 : 01:19:21

Thank you for an immediate response I really appreciate it thanks a lot. However i have another question i want it to have different Period Id per cut off

example

Id Cutoff_start Cutoff_End
17 09/01/2011 09/15/2011
18 09/16/2011 09/30/2011

The Id will be consistent 1-24 for per month has two cut-off. like from the example september has 17 and 18 Id for oct 19 and 20 and so on..

zhel04
Starting Member

38 Posts

Posted - 2011-09-13 : 01:46:04

how would i be able to get the following:

Id Cutoff_start cutoff_end
1 01/01/2011 01/15/2011
2 01/16/2011 01/30/2011
3 02/01/2011 02/15/2011
4 02/16/2011 02/28/2011
.
.
.
.
.
.
.
.
23 12/01/2011 12/15/2011
24 12/16/2011 12/30/2011

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-13 : 02:56:15

[code]select row_number() over(partition by datediff(yy,0,cut_off_1_start) order by cut_off_1_start asc) as id,cut_off_1_start,
cut_off_1_end = dateadd(day, day(cut_off_2_end)/2 - 1, cut_off_1_start),
cut_off_2_start = dateadd(day, day(cut_off_2_end)/2, cut_off_1_start),
cut_off_2_end
from
(
select cut_off_1_start = dateadd(month, (@year - 1900) * 12 + @month - 1, 0),
cut_off_2_end = dateadd(month, (@year - 1900) * 12 + @month, -1)
) d

[/code]

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

zhel04
Starting Member

38 Posts

Posted - 2011-09-13 : 03:04:21

hi.. thanks for your help however you've given me a row number the id in my example is not a row number for every month there is a particular id i just don't know how wil i be able to get it or to put it in a query. Please see below:

Cut-off ID
jan 1-15 1
jan 16-30 2
feb 1-15 3
feb 16-28 4
mar 1-15 5
mar 16-30 6
apr 1-15 7
apr 16-30 8
.
.
.
.
.
.
dec 1-15 23
dec 16-30 24

Id for each cutoff wont change whatever the year is, so when the year changes yet same cutoff it will have same id.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources