advancing dates

• frdrckmitchell7

  Ten Centuries

  Points: 1351

  More actions

  November 20, 2014 at 1:50 am

  #312722

  Hi Clever People

  I have the following codes with hard-coded dates as per below. I have will have to run this code in a procedure every month, which means I will have to change the dates every month. Does anybody have an idea how I can do this without having to change the dates all the time? The months will always be fifteen months ahead.

  Kind regards

  Fred

  WHEN PlannedRelease.TransDateNumeric BETWEEN 20140601 AND 20140630 THEN 1

  WHEN PlannedRelease.TransDateNumeric BETWEEN 20140701 AND 20140731 THEN 2

  WHEN PlannedRelease.TransDateNumeric BETWEEN 20140801 AND 20140831 THEN 3

  WHEN PlannedRelease.TransDateNumeric BETWEEN 20140901 AND 20140930 THEN 4

  WHEN PlannedRelease.TransDateNumeric BETWEEN 20141001 AND 20141030 THEN 5

  WHEN PlannedRelease.TransDateNumeric BETWEEN 20141101 AND 20141130 THEN 6

  WHEN PlannedRelease.TransDateNumeric BETWEEN 20141201 AND 20141231 THEN 7

  WHEN PlannedRelease.TransDateNumeric BETWEEN 20150101 AND 20150131 THEN 8

  WHEN PlannedRelease.TransDateNumeric BETWEEN 20150201 AND 20150228 THEN 9

  WHEN PlannedRelease.TransDateNumeric BETWEEN 20150301 AND 20150331 THEN 10

  WHEN PlannedRelease.TransDateNumeric BETWEEN 20150401 AND 20150430 THEN 11

  WHEN PlannedRelease.TransDateNumeric BETWEEN 20150501 AND 20150531 THEN 12

  WHEN PlannedRelease.TransDateNumeric BETWEEN 20150601 AND 20150130 THEN 13

  WHEN PlannedRelease.TransDateNumeric BETWEEN 20150701 AND 20150731 THEN 14

  WHEN PlannedRelease.TransDateNumeric BETWEEN 20150801 AND 20150831 THEN 15

  WHEN PlannedRelease.TransDateNumeric BETWEEN 20140601 AND 20150831 THEN 99

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  November 20, 2014 at 2:36 am

  #1759755

  Your condition for 99 is never met, check your logic.

  Here's a method to calculate the dates dynamically:

  DECLARE @startDate int = 20140601;

  WITH SampleData AS (

  SELECT *

  FROM (

  VALUES

  (20140601),

  (20140701),

  (20140801),

  (20140901),

  (20141001),

  (20141101),

  (20141201),

  (20150101),

  (20150201),

  (20150301),

  (20150401),

  (20150501),

  (20150601),

  (20150701),

  (20150801),

  (20140601)

  ) AS data (TransDateNumeric)

  )

  SELECT TransDateNumeric,

  result = ISNULL((

  SELECT i + 1

  FROM (

  VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)

  ) AS data(i)

  WHERE CONVERT(date,CAST(TransDateNumeric AS char(8)),112)

  BETWEENDATEADD(month,i,CONVERT(date,CAST(@startDate AS char(8)),112))

  AND DATEADD(day,-1,DATEADD(month,i+1,CONVERT(date,CAST(@startDate AS char(8)),112)))

  ),99)

  FROM SampleData AS PlannedRelease

  Hope this helps

  Gianluca

  -- Gianluca Sartori

• ScottPletcher

  SSC Guru

  Points: 101098

  More actions

  November 20, 2014 at 10:50 am

  #1759921

  I wasn't not sure what '20140601' was relative to the run date. Is it the current month, the next month, 6 months ago?

  Therefore, I used @month_1 to hold the first month. Set that value however you need to. Then the calculation should be what you want.

  DECLARE @month_1 datetime;

  SET @month_1 = '20140601';

  SELECT

  PlannedRelease.TransDateNumeric

  , DATEDIFF(MONTH, @month_1, CAST(PlannedRelease.TransDateNumeric AS varchar(8))) + 1

  FROM (

  SELECT 20140605 AS TransDateNumeric UNION ALL

  SELECT 20140714 AS TransDateNumeric UNION ALL

  SELECT 20140811 UNION ALL

  SELECT 20140922 UNION ALL

  SELECT 20141002 UNION ALL

  SELECT 20141121 UNION ALL

  SELECT 20141217 UNION ALL

  SELECT 20150116 UNION ALL

  SELECT 20150207 UNION ALL

  SELECT 20150308 UNION ALL

  SELECT 20150409 UNION ALL

  SELECT 20150503 UNION ALL

  SELECT 20150617 UNION ALL

  SELECT 20150726 UNION ALL

  SELECT 20150830

  ) AS PlannedRelease

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• frdrckmitchell7

  Ten Centuries

  Points: 1351

  More actions

  November 20, 2014 at 11:16 pm

  #1760006

  Thank you both for your reply but the integer dates are still hard-coded in your answers. And that's the problem that I have. Instead of 20141106 (hard-coded) I want an alternative where I do not hard-code it. Write it away to a temp-table perhaps and use what is in the temp-table - not what is hard-coded as is now.

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  November 21, 2014 at 1:38 am

  #1760026

  frdrckmitchell7 (11/20/2014)

  ---

  Thank you both for your reply but the integer dates are still hard-coded in your answers. And that's the problem that I have. Instead of 20141106 (hard-coded) I want an alternative where I do not hard-code it. Write it away to a temp-table perhaps and use what is in the temp-table - not what is hard-coded as is now.

  It's not hardcoded, it's a variable.

  You can assign whatever you want to that variable. It could be a stored procedure parameter, a value you read from a table... whatever.

  -- Gianluca Sartori

• frdrckmitchell7

  Ten Centuries

  Points: 1351

  More actions

  November 21, 2014 at 1:50 am

  #1760032

  Oh, ok. Thanks. It worked!