Getting the first date of next year

  • Hi All,

    I have written a code recently to get the first date of next year.

    say for example,

    Create table t1

    (

    Eno int,

    Date datetime

    )

    Insert into t1

    select 1,'Jun/30/2006'

    union all

    select 2,'May/31/2004'

    union all

    select 3,'Jan/01/2007'

    union all

    select 4,'Aug/10/2005'

    Expected Output:

    Eno InceptionDate

    1 01/01/2007

    2 01/01/2005

    3 01/01/2007 - No change

    4 01/01/2006

    I have written the below query.

    Select Eno,InceptionDate = case when Datepart(dd,Date) = 1 And Datepart(mm,Date) = 1 Then Date

    else '01/'01/' + convert(varchar(4),datepart(yy,Date)+1)

    end

    from t1

    Alternate ways are welcome !

    karthik

  • Count number of whole years between the date and "zero date" (1/01/1900) and add this number +1 to "zero date".

    It's gonna be beginning of next year.

    Use datetime functions for it.

    Same method may be used for month, weeks etc.

    _____________
    Code for TallyGenerator

  • Sergiy, that will not work for January 1st dates.

    However, you are not that far from a working suggestion.

    Try this

    DECLARE@Sample TABLE

    (

    Eno int,

    Date datetime

    )

    Insert@Sample

    select 1,'2006-06-30' union all

    select 2,'2004-05-31' union all

    select 3,'2007-01-01' union all

    select 4,'2005-08-10'

    SELECTEno,

    Date AS [Original date],

    DATEADD(YEAR, DATEDIFF(YEAR, '19000101', Date) + 1, '19000101') AS Sergiy,

    DATEADD(YEAR, DATEDIFF(YEAR, '19000101', Date - 1), '19010101') AS Peso

    FROM@Sample


    N 56°04'39.16"
    E 12°55'05.25"

  • HEre is another solution

    DECLARE @Sample TABLE

    (

    Eno int,

    Date datetime

    )

    Insert @Sample

    select 1,'2006-06-30' union all

    select 2,'2004-05-31' union all

    select 3,'2007-01-01' union all

    select 4,'2005-08-10'

    SELECT

    Eno,

    Date AS [Original date],

    DATEADD(YEAR,DATEDIFF(YEAR,0,Date-1)+1,0)

    FROM @Sample

    I simply use 0 as the base date

    thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • Peso (9/1/2008)


    Sergiy, that will not work for January 1st dates.

    However, you are not that far from a working suggestion.

    Thanks, Peso, it's good to know there is some tester always around to implement and test my ideas. 😉

    I'd doubt your consideration about "working suggestion" though.

    As any number with limited precision a datetime value indicates infinite number of time moments between the value itself and next precision step.

    So, value '20070101' indicates any time moment >= '2007-01-01 00:00:00.000' and < '2007-01-01 00:00:00.003'

    If the system records only dates, not times, then its precision equals 1 day and '20070101' indicates any time >= '2007-01-01 00:00:00.000' and < '2007-01-02 00:00:00.000'

    Because there is infinite number (it's infinitely more than any very big number) of moments withing any of those periods then probability of equality (= '2007-01-01 00:00:00.000' ) is zero. So, it may be safely taken as > '2007-01-01 00:00:00.000'

    If your current time is '2007-01-01' then you've already entered the year 2007 and the beginning of your next year is '2008-01-01'.

    If to look at the resultset returned by your test code it becomes clear that my result is right and your one is wrong.

    _____________
    Code for TallyGenerator

  • Not according to OP requirements.

    He is only storing dates with "no time information", ie time set to 00:00:00

    And he wants dates for January 1st of every year to remain to current year, and all other dates be forwarded to January 1st of following year.

    Didn't you read the spec?


    N 56°04'39.16"
    E 12°55'05.25"

Viewing 6 posts - 1 through 5 (of 5 total)

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