January 15, 2016 at 5:00 pm
Can anyone help me with this. I am trying to create a start date and end based on the current date. The code is not calculating properly if my startdate falls in the previous year.
Declare @StartDate DATETIME,
@Cnt1 int,
@Cnt int,
@EndDate DATETIME
SELECT @StartDate = CAST(CONVERT(VARCHAR(4), DATEPART(yyyy, GETDATE())) + '-' + CONVERT(VARCHAR(2), DATEPART(mm, GETDATE())) + '-01' AS DATETIME)
SELECT @StartDate = @StartDate - 1
SELECT @Cnt1 = convert (varchar(2) ,datepart(dd,GETDATE()-@StartDate))
if @Cnt1 >7
begin
SELECT @StartDate = CAST(CONVERT(VARCHAR(5),DATEPART(yyyy, GETDATE() -7)) + '-' + CONVERT(VARCHAR(2),DATEPART(mm, GETDATE() - 15)) + '-' + CONVERT(VARCHAR(2), DATEPART(dd, GETDATE() - 15)) AS DATETIME)
end
SELECT @EndDate = CAST(CONVERT(VARCHAR(5),DATEPART(yyyy, GETDATE() + 1)) + '-' + CONVERT(VARCHAR(2),DATEPART(mm, GETDATE() + 1)) + '-' + CONVERT(VARCHAR(2), DATEPART(dd, GETDATE() + 1)) AS DATETIME)
SET @Cnt = 0
Print @Cnt1
Print @Cnt
Print @startDate
Print @EndDate
Result I am getting :
16
0
Dec 31 2016 12:00AM
Jan 16 2016 12:00AM
but It should be Dec 31, 2015
January 15, 2016 at 5:54 pm
I think this will do the same thing, much more simply and clearly:
Declare @StartDate DATETIME,
@EndDate DATETIME
if DAY(@StartDate) >= 7
set @StartDate = DATEADD(day, -15, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
else
set @StartDate = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
set @EndDate = DATEADD(day, 1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
Print @startDate
Print @EndDate
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 15, 2016 at 6:19 pm
Thanks a bunch Scott..
January 16, 2016 at 1:17 am
Scott's fine code provides the correct answer but logic embedded in the code such as this is neither too readable nor maintainable and what is even worse is that it's not "inlinable" which means it cannot be used in an inline table-valued function which is the most effective way of encapsulating and reusing logic.
😎
Here is a quick alternative solution:
USE tempdb;
GO
SET NOCOUNT ON;
/*
Calculating a period based on how far into the month the
reference day is (@CALC_DATE) based on the value of the
@TOGGLE_DAY parameter. If @CALC_DATE is greater or equal
to @TOGGLE_DAY then start day will be @TOGGLE_LEN days
before @CALC_DATE otherwise the last day of the previous
month.
*/
DECLARE @CALC_DATE DATETIME = '2016-01-09 15:35';
DECLARE @TOGGLE_DAY INT = 7;
DECLARE @TOGGLE_LEN INT = 15;
/*
PERIOD_SELECTION
CALC_DATE strips out the time part of the incoming date
DAY_OF_MONTH is the Day Of Month value
PERIOD_FLAG returns 1 if DAY_OF_MONTH is equal or greater
than @TOGGLE_LEN otherwise 0
*/
;WITH PERIOD_SELECTION(CALC_DATE,DAY_OF_MONTH,PERIOD_FLAG) AS
(
SELECT
CONVERT(DATETIME,CONVERT(DATE,@CALC_DATE,0),0) AS CALC_DATE
,DAY(@CALC_DATE) AS DAY_OF_MONTH
,(SIGN(FLOOR(DAY(@CALC_DATE) / @TOGGLE_DAY))) AS PERIOD_FLAG
)
/*
PERIOD_CALCULATION
CALC_DATE (unchanged from PERIOD_SELECTION)
PGET is the value to shift if PERIOD_FLAG = 1
PLTT is the value to shift if PERIOD_FLAG = 0
*/
,PERIOD_CALCULATION(CALC_DATE,PGET,PLTT) AS
(
SELECT
PS.CALC_DATE
,(PS.PERIOD_FLAG * @TOGGLE_LEN) AS PGET
,PS.DAY_OF_MONTH * (1 - PS.PERIOD_FLAG) AS PLTT
FROM PERIOD_SELECTION PS
)
/*
THE_PERIOD
START_DATE is the first date of the period
END_DATE is the last date of the period
*/
,THE_PERIOD (START_DATE,END_DATE) AS
(
SELECT
DATEADD(DAY,-(PC.PGET + PC.PLTT),PC.CALC_DATE) AS START_DATE
,DATEADD(DAY,1,PC.CALC_DATE) AS END_DATE
FROM PERIOD_CALCULATION PC
)
SELECT
TP.START_DATE
,TP.END_DATE
FROM THE_PERIOD TP;
January 19, 2016 at 6:22 pm
Hi Scott,
I executed the code today..
Declare @StartDate Datetime,
@EndDate Datetime
if DAY(@StartDate) >= 7
set @StartDate = DATEADD(day, -15, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
else
set @StartDate = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
set @EndDate = DATEADD(day, 1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
Print @StartDate
Print @EndDate
The result I am getting..
Dec 31 2015 12:00AM
Jan 20 2016 12:00AM
The Query is not providing the correct resultset.. Can you help
January 19, 2016 at 7:25 pm
SumonB (1/19/2016)
Hi Scott,I executed the code today..
Declare @StartDate Datetime,
@EndDate Datetime
if DAY(@StartDate) >= 7
set @StartDate = DATEADD(day, -15, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
else
set @StartDate = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
set @EndDate = DATEADD(day, 1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
Print @StartDate
Print @EndDate
The result I am getting..
Dec 31 2015 12:00AM
Jan 20 2016 12:00AM
The Query is not providing the correct resultset.. Can you help
your problem is here:
Declare @StartDate Datetime,
@EndDate Datetime
if DAY(@StartDate) >= 7
No value is yet assigned to @StartDate, so DAY(@StartDate) is always NULL.
The script always executes "else" part.
_____________
Code for TallyGenerator
January 19, 2016 at 8:05 pm
SumonB (1/15/2016)
Can anyone help me with this. I am trying to create a start date and end based on the current date. The code is not calculating properly if my startdate falls in the previous year.Declare @StartDate DATETIME,
@Cnt1 int,
@Cnt int,
@EndDate DATETIME
SELECT @StartDate = CAST(CONVERT(VARCHAR(4), DATEPART(yyyy, GETDATE())) + '-' + CONVERT(VARCHAR(2), DATEPART(mm, GETDATE())) + '-01' AS DATETIME)
SELECT @StartDate = @StartDate - 1
SELECT @Cnt1 = convert (varchar(2) ,datepart(dd,GETDATE()-@StartDate))
if @Cnt1 >7
begin
SELECT @StartDate = CAST(CONVERT(VARCHAR(5),DATEPART(yyyy, GETDATE() -7)) + '-' + CONVERT(VARCHAR(2),DATEPART(mm, GETDATE() - 15)) + '-' + CONVERT(VARCHAR(2), DATEPART(dd, GETDATE() - 15)) AS DATETIME)
end
SELECT @EndDate = CAST(CONVERT(VARCHAR(5),DATEPART(yyyy, GETDATE() + 1)) + '-' + CONVERT(VARCHAR(2),DATEPART(mm, GETDATE() + 1)) + '-' + CONVERT(VARCHAR(2), DATEPART(dd, GETDATE() + 1)) AS DATETIME)
SET @Cnt = 0
Print @Cnt1
Print @Cnt
Print @startDate
Print @EndDate
Result I am getting :
16
0
Dec 31 2016 12:00AM
Jan 16 2016 12:00AM
but It should be Dec 31, 2015
There was a simple error in your original calculations:
DATEPART(yyyy, GETDATE() -7))
Must be "-15" as for all other date parts in that line.
But the whole approach looks pretty strange to me.
On 6th Feb it's gonna give you:
@StartDate = Jan 31 2016 and @EndDate = Feb 7 2016
But on 7th Feb it's gonna be:
@StartDate = Jan 23 2016 and @EndDate = Feb 8 2016
On the current date increase the @StartDate jumps back - is it how it's expected?
_____________
Code for TallyGenerator
January 19, 2016 at 8:28 pm
Here the query I used to test the outcome:
SELECT DO.RunTime,
DATEPART(dd, DateOnly),
CASE WHEN DATEPART(dd, DateOnly) > DO.Threshold
THEN DATEADD(dd, -15, DateOnly)
ELSE DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, DateOnly), 0) )
END StartDate,
DATEADD(dd, 1, DateOnly) EndDate
FROM (
SELECT DT.RunTime, Threshold, DATEADD(dd, DATEDIFF(dd, 0, DT.RunTime), 0) DateOnly
FROM (
SELECT DATEADD(n, 28, DATEADD(hh, 11, DATEADD(dd, N, '20151201') ) ) RunTime, 7 Threshold
FROM dbo.TallyGenerator(0, 400, NULL, 1) tg
) DT
) DO
Here's what it generated for January and February of this year:
2016-01-01 11:28:00.000 1 2015-12-31 2016-01-02
2016-01-02 11:28:00.000 2 2015-12-31 2016-01-03
2016-01-03 11:28:00.000 3 2015-12-31 2016-01-04
2016-01-04 11:28:00.000 4 2015-12-31 2016-01-05
2016-01-05 11:28:00.000 5 2015-12-31 2016-01-06
2016-01-06 11:28:00.000 6 2015-12-31 2016-01-07
2016-01-07 11:28:00.000 7 2015-12-31 2016-01-08
2016-01-08 11:28:00.000 8 2015-12-24 2016-01-09
2016-01-09 11:28:00.000 9 2015-12-25 2016-01-10
2016-01-10 11:28:00.000 10 2015-12-26 2016-01-11
2016-01-11 11:28:00.000 11 2015-12-27 2016-01-12
2016-01-12 11:28:00.000 12 2015-12-28 2016-01-13
2016-01-13 11:28:00.000 13 2015-12-29 2016-01-14
2016-01-14 11:28:00.000 14 2015-12-30 2016-01-15
2016-01-15 11:28:00.000 15 2015-12-31 2016-01-16
2016-01-16 11:28:00.000 16 2016-01-01 2016-01-17
2016-01-17 11:28:00.000 17 2016-01-02 2016-01-18
2016-01-18 11:28:00.000 18 2016-01-03 2016-01-19
2016-01-19 11:28:00.000 19 2016-01-04 2016-01-20
2016-01-20 11:28:00.000 20 2016-01-05 2016-01-21
2016-01-21 11:28:00.000 21 2016-01-06 2016-01-22
2016-01-22 11:28:00.000 22 2016-01-07 2016-01-23
2016-01-23 11:28:00.000 23 2016-01-08 2016-01-24
2016-01-24 11:28:00.000 24 2016-01-09 2016-01-25
2016-01-25 11:28:00.000 25 2016-01-10 2016-01-26
2016-01-26 11:28:00.000 26 2016-01-11 2016-01-27
2016-01-27 11:28:00.000 27 2016-01-12 2016-01-28
2016-01-28 11:28:00.000 28 2016-01-13 2016-01-29
2016-01-29 11:28:00.000 29 2016-01-14 2016-01-30
2016-01-30 11:28:00.000 30 2016-01-15 2016-01-31
2016-01-31 11:28:00.000 31 2016-01-16 2016-02-01
2016-02-01 11:28:00.000 1 2016-01-31 2016-02-02
2016-02-02 11:28:00.000 2 2016-01-31 2016-02-03
2016-02-03 11:28:00.000 3 2016-01-31 2016-02-04
2016-02-04 11:28:00.000 4 2016-01-31 2016-02-05
2016-02-05 11:28:00.000 5 2016-01-31 2016-02-06
2016-02-06 11:28:00.000 6 2016-01-31 2016-02-07
2016-02-07 11:28:00.000 7 2016-01-31 2016-02-08
2016-02-08 11:28:00.000 8 2016-01-24 2016-02-09
2016-02-09 11:28:00.000 9 2016-01-25 2016-02-10
2016-02-10 11:28:00.000 10 2016-01-26 2016-02-11
2016-02-11 11:28:00.000 11 2016-01-27 2016-02-12
2016-02-12 11:28:00.000 12 2016-01-28 2016-02-13
2016-02-13 11:28:00.000 13 2016-01-29 2016-02-14
2016-02-14 11:28:00.000 14 2016-01-30 2016-02-15
2016-02-15 11:28:00.000 15 2016-01-31 2016-02-16
2016-02-16 11:28:00.000 16 2016-02-01 2016-02-17
2016-02-17 11:28:00.000 17 2016-02-02 2016-02-18
2016-02-18 11:28:00.000 18 2016-02-03 2016-02-19
2016-02-19 11:28:00.000 19 2016-02-04 2016-02-20
2016-02-20 11:28:00.000 20 2016-02-05 2016-02-21
2016-02-21 11:28:00.000 21 2016-02-06 2016-02-22
2016-02-22 11:28:00.000 22 2016-02-07 2016-02-23
2016-02-23 11:28:00.000 23 2016-02-08 2016-02-24
2016-02-24 11:28:00.000 24 2016-02-09 2016-02-25
2016-02-25 11:28:00.000 25 2016-02-10 2016-02-26
2016-02-26 11:28:00.000 26 2016-02-11 2016-02-27
2016-02-27 11:28:00.000 27 2016-02-12 2016-02-28
2016-02-28 11:28:00.000 28 2016-02-13 2016-02-29
2016-02-29 11:28:00.000 29 2016-02-14 2016-03-01
To be honest, it does not look quite right to me.
_____________
Code for TallyGenerator
January 20, 2016 at 7:41 am
Sergiy (1/19/2016)
SumonB (1/19/2016)
Hi Scott,I executed the code today..
Declare @StartDate Datetime,
@EndDate Datetime
if DAY(@StartDate) >= 7
set @StartDate = DATEADD(day, -15, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
else
set @StartDate = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
set @EndDate = DATEADD(day, 1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
Print @StartDate
Print @EndDate
The result I am getting..
Dec 31 2015 12:00AM
Jan 20 2016 12:00AM
The Query is not providing the correct resultset.. Can you help
your problem is here:
Declare @StartDate Datetime,@EndDate Datetime
if DAY(@StartDate) >= 7
No value is yet assigned to @StartDate, so DAY(@StartDate) is always NULL.
The script always executes "else" part.
I only coded the part of the code I changed ... the existing SETs were naturally required to still be there.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 20, 2016 at 10:13 am
Hi Scott,
I just need the current date and a date before 15 days to find out whatever backups I took during the last 15 days.
I changed the code as, is this code seems okay to you?
Declare @StartDate Datetime,
@EndDate Datetime
set @StartDate=GETDATE()
if DAY(@StartDate) >= 7
set @StartDate = DATEADD(day, -15, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
else
set @StartDate = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
set @EndDate = DATEADD(day, 1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
Print @StartDate
Print @EndDate
Result:
Jan 5 2016 12:00AM
Jan 21 2016 12:00AM
January 20, 2016 at 10:21 am
If you always need exactly 15 days, just do this:
Declare @StartDate Datetime,
@EndDate Datetime
set @EndDate=DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) --strip time from date
set @StartDate = DATEADD(DAY, -15, @EndDate)
Print @StartDate
Print @EndDate
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 20, 2016 at 10:27 am
What if today's date is 5th January' 2016.
Will it show the start date as 21st December'2015 and End date as 5th January'2016
January 20, 2016 at 11:00 am
SumonB (1/20/2016)
What if today's date is 5th January' 2016.Will it show the start date as 21st December'2015 and End date as 5th January'2016
Yes ... and here's the proof:
Declare @StartDate Datetime,
@EndDate Datetime
set @EndDate=DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) --strip time from date
set @EndDate='20160105' --override end date to test a specific date
set @StartDate = DATEADD(DAY, -15, @EndDate)
Print @StartDate
Print @EndDate
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 20, 2016 at 11:33 am
SumonB (1/20/2016)
Hi Scott,I just need the current date and a date before 15 days to find out whatever backups I took during the last 15 days.
I changed the code as, is this code seems okay to you?
Declare @StartDate Datetime,
@EndDate Datetime
set @StartDate=GETDATE()
if DAY(@StartDate) >= 7
set @StartDate = DATEADD(day, -15, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
else
set @StartDate = DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
set @EndDate = DATEADD(day, 1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
Print @StartDate
Print @EndDate
Result:
Jan 5 2016 12:00AM
Jan 21 2016 12:00AM
OK, from your updated description I can see where number 15 comes from.
But I cannot see what
if @Cnt1 >7
is about.
What was the purpose of that check
_____________
Code for TallyGenerator
January 20, 2016 at 3:22 pm
This is used because if Current Date is less than 7th of the month, it should go to previous month to set the start date
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply