February 5, 2017 at 8:42 am
I'm trying to develop a purchasing calendar. I have a starting date that is static and a number of cycle days that are also static. If today's date is greater than the start date + cycle days, I need to know the number of days. If it is less, I need start + cycle. I have this so far;
DECLARE @SD datetime, @cycle numeric SET @SD = '1-1-17' SET @cycle = 21
SELECT CASE WHEN GETDATE() > @SD + @cycle THEN DATEDIFF(day, @SD, GETDATE())
ELSE @cycle + @SD END AS 'NOD' /* NOD = Next Order Date*/
SELECT DATEDIFF(day, @SD, GETDATE())
When I run the first SELECT I get a weird value of 1900-02-05 00:00:00. I expected to see what I get when I run the second SELECT which is 35. 35 is the difference between GETDATE and 1-1-17. Why am I getting the weird 1900 date and/or how do I get the first query to return 35?
February 5, 2017 at 9:02 am
jcobb 20350 - Sunday, February 5, 2017 8:42 AMI'm trying to develop a purchasing calendar. I have a starting date that is static and a number of cycle days that are also static. If today's date is greater than the start date + cycle days, I need to know the number of days. If it is less, I need start + cycle. I have this so far;
DECLARE @SD datetime, @cycle numeric SET @SD = '1-1-17' SET @cycle = 21
SELECT CASE WHEN GETDATE() > @SD + @cycle THEN DATEDIFF(day, @SD, GETDATE())
ELSE @cycle + @SD END AS 'NOD' /* NOD = Next Order Date*/SELECT DATEDIFF(day, @SD, GETDATE())
When I run the first SELECT I get a weird value of 1900-02-05 00:00:00. I expected to see what I get when I run the second SELECT which is 35. 35 is the difference between GETDATE and 1-1-17. Why am I getting the weird 1900 date and/or how do I get the first query to return 35?
If I format your code slightly differently, the problem becomes obvious:
DECLARE @SD DATETIME,
@cycle NUMERIC;
SET @SD = '1-1-17';
SET @cycle = 21;
SELECT NOD = CASE
WHEN GETDATE() > @SD + @cycle THEN
DATEDIFF(DAY, @SD, GETDATE())
ELSE
@cycle + @SD
END; /* NOD = Next Order Date*/
SELECT DATEDIFF(DAY, @SD, GETDATE());
Part 1 of your CASE returns an INT, part 2 returns a Datetime. Datetime takes precedence, so that's what you get.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
February 5, 2017 at 10:34 am
Thanks Phil. You got me on the right path. This is what I'm going to try out.
DECLARE @SD datetime, @ord_cycle numeric
SET @SD = '1-1-17' SET @ord_cycle = 30
/* Get number of days beyond the start date*/
; WITH D AS
(SELECT DATEDIFF(day,@SD, GETDATE()) AS 'Last')
SELECT CASE WHEN D.Last > @ord_cycle THEN
/* When CTE days are greater than the cycle days, divide by the cycle to see
how many periods have passed, then round up with CEILING.
The round up * the cycle * original start date, gives the next order date */
CEILING(D.Last/@ord_cycle) * @ord_cycle + @SD
/* If CTE days are less than cycle use cycle + orinial start date to get next order date.
This will only happen once in the life cycle */
ELSE @ord_cycle + @SD END AS 'DD'
FROM D
Edit: Above should be + start date not * as shown. Correction: The round up * the cycle + original start date
February 6, 2017 at 4:36 am
Look at below code, see if it give the correct resultsDECLARE @SD DATETIME
,@cycle NUMERIC
SET @SD = '1-1-17'
SET @cycle = 21
SELECT CASE
WHEN GETDATE() > @cycle + @SD
THEN GETDATE() + @cycle
ELSE @cycle + @SD
END AS 'NOD' /* NOD = Next Order Date*/
Thanks & regards,
Shankar Walvekar
February 7, 2017 at 9:20 am
i was trying other aproach
DECLARE @SD datetime, @ord_cycle numeric
SELECT @SD = '2017-03-01', @ord_cycle = 21
SELECT ISNULL(
NULLIF(
(CEILING(DATEDIFF(day, @SD, GETDATE()) / @ord_cycle))
, 0)
, 1)
* @ord_cycle + @SD
... doesn't go in the right way ... 🙁
but didn't give up and end up with this, check it out
DECLARE @SD datetime, @ord_cycle numeric
SELECT @SD = '2017-03-01', @ord_cycle = 21
SELECT ISNULL(
NULLIF(
(ABS(CEILING(DATEDIFF(day, @SD, GETDATE()) / @ord_cycle))
+ CEILING(DATEDIFF(day, @SD, GETDATE()) / @ord_cycle)) / 2
, 0)
, 1)
* @ord_cycle + @SD
some credits to The Dixie Flatline in this post
February 9, 2017 at 11:16 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply