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

  • 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

  • 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]

  • 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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply