Generate a day date according to existing month+year+daydiff

• avishain

  Mr or Mrs. 500

  Points: 506

  More actions

  August 1, 2013 at 1:46 am

  #281305

  im using sql 2008.

  i got this 5 columns:

  StartMonth,StratYear,EndMonth,EndYear,DaysBetween

  i dont have the day of these dates and that's what im trying to generate for example:

  12 2008 1 2009 8

  now, i want to create a random date (start date and end date , format as dd/mm/yyyy) which will include the day and will make scene upon the data i have under days between

  FOR INSTANCE,

  if i know that i got 8 days (DaysBetween) and the startmonth is 12, the date must be from 24/12/2008 cause if i add 8 days i get the EndMonth (1/2009)

  if i would choose the date 2/12/2008 i would get 10/12/2008 and its not good cause the month is still 12....and i need 1 (2009)

  how can i generate valid dates ?

  thank you

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  August 1, 2013 at 4:06 am

  #1637709

  What about if there is a two months difference between given Start and End and you want consiquent 8 days to generate?

  StartMonth,StratYear,EndMonth,EndYear,DaysBetween

  12 2008 2 2009 8

  It would be highly beneficial if you could follow the tips from here:

  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  Provide few more examples with expected results!

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]

• WolfgangE

  SSCertifiable

  Points: 6328

  More actions

  August 3, 2013 at 1:19 pm

  #1638436

  If I understand it right the EndMonth and EndYear is always a date first of month. Let's take this as EndDate.

  StartMonth and StartYear is a date that is a number of days before StartDate.

  So we just have to substract the value of DaysBetween from the EndDate.

  Let's say that the StartMonth and StartYear are always defined in the right way (maybe you substract 8 days but the StartMonth is back more than e.g. 5 months, who secures this?) then just use the dateadd function. The rest is formatting the datetime value.

  declare @data table(

  StartMonth int,

  StartYear int,

  EndMonth int,

  EndYear int,

  DaysBetween int

  );

  insert into @data values

  ( 12, 2008, 1, 2009, 8 );

  select

  convert( char(10),

  dateadd(

  day,

  -8,

  -- create EndDate

  cast( EndYear as char(4) ) +

  cast( replicate( '0', 2 - len(EndMonth) ) + cast( EndMonth as varchar(2) ) as char(2) ) +

  '01'

  )

  ,103 )

  from @data