last day of any year

  • hello fellas,i need some code or logic that will always provide the last day of any year.i know its a vague question but any help is appreaciated. thanks

  • Here is an example for the current year:

    select LastDayOfYear = dateadd(yy,datediff(yy,-1,getdate()),-1)

    Results:

    LastDayOfYear

    -----------------------

    2012-12-31 00:00:00.000

    (1 row(s) affected)

    For a different year, just replace getdate() with a date in that year.

  • The question I have, is this, the last date of what year? Using today, 2012-07-02, as the date of reference, what year do you want the end of; this year, next year, or last year?

    The answer above works for this year.

  • The question I have is how are you going to use this? If you are using this in a where clause it is most likely a better option to get the first of the next year (current year) and use less than.

    Example:

    WHERE datecolumn >= {first of last year}

    AND datecolumn < {first of this year}

    This will make sure you include all times on the last day of the year - regardless of the precision of the date/time column being used.

    Using this:

    WHERE datecolumn BETWEEN {first of last year} AND {end of last year}

    could exclude everything on the last day of the year where the time is greater than 00:00:00.000.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • hello fellas,i need some code or logic that will always provide the last day of any year.i know its a vague question but any help is appreaciated. thanks

    Last day will always be 12/31 so why not concantenate '12/31/' with year and then convert to a date or datetime.

    I do not see much point of its usage but you can try below to find the last day of year..

    drop function LastDayOfAnyYear

    go

    create function LastDayOfAnyYear

    (

    @year int

    )

    returns table

    as

    return

    select CONVERT(date,'12/31/' + convert(varchar(4),@year),101) LastDayOfYear

    go

    declare @year int = 2012

    select * from LastDayOfAnyYear(@year)

    --or you could use it with the tnumbers table as welll

    select * from nums n

    cross apply LastDayOfAnyYear(n.cnt)

    where n.cnt between 1900 and 9999

    GulliMeel

    Finding top n Worst Performing queries[/url]
    Improve the performance of Merge Join(special case)
    How to Post Performance Problem -Gail Shaw[/url]

  • To convert an integer year number to the last day of the year, you can use DATEADD without the need to convert to a character string:

    -- Convert year number to last day of year

    select

    a.*,

    LastDay = dateadd(yy,a.YearNum-1899,-1)

    from

    ( -- Test Data

    select YearNum = 1753 union all

    select YearNum = 1899 union all

    select YearNum = 1900 union all

    select YearNum = 2000 union all

    select YearNum = 2001 union all

    select YearNum = 2004 union all

    select YearNum = 2011 union all

    select YearNum = 2012 union all

    select YearNum = 9999

    ) a

    order by

    a.YearNum

    Results:

    YearNum LastDay

    ----------- -----------------------

    1753 1753-12-31 00:00:00.000

    1899 1899-12-31 00:00:00.000

    1900 1900-12-31 00:00:00.000

    2000 2000-12-31 00:00:00.000

    2001 2001-12-31 00:00:00.000

    2004 2004-12-31 00:00:00.000

    2011 2011-12-31 00:00:00.000

    2012 2012-12-31 00:00:00.000

    9999 9999-12-31 00:00:00.000

    (9 row(s) affected)

  • Starting at the beginning, do you mean Last Day of:

    1) The Fiscal Year (the business's accounting year)

    2) The Calendar Year

    3) The Business Year (slightly different from Fiscal)

    4) The PayDay Year cycle

    5) The actual Day of the last day of the year (Monday, Tuesday, Wednesday, etc.)

    ???

    It makes a difference in how we answer the question.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • hello fellas,i need some code or logic that will always provide the last day of any year.i know its a vague question but any help is appreaciated. thanks

    I think as its the universal truth that 31st dec of any year is the last day of calendar year, then instead of searching it by logic, you should hard code it.

    Also if you looking for the day of that particular date then you can use day().

    For ex, day(getdate()) will return 5 which means Thursday. This counting starts from 0 as Sunday and then onwards.

    ----------
    Ashish

  • crazy4sql (7/5/2012)Also if you looking for the day of that particular date then you can use day().

    For ex, day(getdate()) will return 5 which means Thursday. This counting starts from 0 as Sunday and then onwards.

    Actually, day returns the day of the month, not the weekday--day(getdate()) today will return 6, for instance, but that's because it's the 6th July, not because it's Friday. You would have to use DATEPART(dw, getdate()) to return the day of the week, that's a 1-based value, not zero-based, and what weekday is number 1 depends on what SET DATEFIRST is configured to--yes, that would be Sunday on a default install of a US English version of SQL Server, but isn't guaranteed to be that way for every installation.

  • Gullimeel (7/4/2012)


    hello fellas,i need some code or logic that will always provide the last day of any year.i know its a vague question but any help is appreaciated. thanks

    Last day will always be 12/31 so why not concantenate '12/31/' with year and then convert to a date or datetime.

    I do not see much point of its usage but you can try below to find the last day of year..

    drop function LastDayOfAnyYear

    go

    create function LastDayOfAnyYear

    (

    @year int

    )

    returns table

    as

    return

    select CONVERT(date,'12/31/' + convert(varchar(4),@year),101) LastDayOfYear

    go

    declare @year int = 2012

    select * from LastDayOfAnyYear(@year)

    --or you could use it with the tnumbers table as welll

    select * from nums n

    cross apply LastDayOfAnyYear(n.cnt)

    where n.cnt between 1900 and 9999

    You asked why not hard code and use CONVERT(date,'12/31/' + convert(varchar(4),@year),101) LastDayOfYear. How about because it is slower.

    I ran the following code several times and this, CAST(CAST(MyYear AS CHAR(4)) + '-12-31' AS DATE), was about twice as slow this, CAST(DATEADD(yy, MyYear - 1899, -1) AS DATE) when run over 1,000,000 rows of test data.

    SET NOCOUNT ON;

    DECLARE @MyDate DATE;

    SET STATISTICS TIME ON;

    WITH e1(n) AS (SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d(n)),

    e2(n) AS (SELECT a.n FROM e1 a CROSS JOIN e1 b), -- 100 rows

    e4(n) AS (SELECT a.n FROM e2 a CROSS JOIN e2 b), -- 10,000 rows

    e6(n) AS (SELECT a.n FROM e4 a CROSS JOIN e2 b), -- 1,000,000 rows

    tally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT(NULL))) n FROM e6),

    sampledata AS (SELECT 2000 + FLOOR(10 * rand(CHECKSUM(NEWID()))) AS MyYear FROM tally)

    SELECT

    @MyDate = CAST(CAST(MyYear AS CHAR(4)) + '-12-31' AS DATE)

    FROM

    sampledata;

    SET STATISTICS TIME OFF;

    GO

    DECLARE @MyDate DATE;

    SET STATISTICS TIME ON;

    WITH e1(n) AS (SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d(n)),

    e2(n) AS (SELECT a.n FROM e1 a CROSS JOIN e1 b), -- 100 rows

    e4(n) AS (SELECT a.n FROM e2 a CROSS JOIN e2 b), -- 10,000 rows

    e6(n) AS (SELECT a.n FROM e4 a CROSS JOIN e2 b), -- 1,000,000 rows

    tally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT(NULL))) n FROM e6),

    sampledata AS (SELECT 2000 + FLOOR(10 * rand(CHECKSUM(NEWID()))) AS MyYear FROM tally)

    SELECT

    @MyDate = CAST(DATEADD(yy, MyYear - 1899, -1) AS DATE)

    FROM

    sampledata;

    SET STATISTICS TIME OFF;

    GO

    SET NOCOUNT OFF;

  • How about create procedure sp_lastdayofyear (@year int) as

    select Convert(date,convert(varchar(4),@year)+'-12-31')

  • dan-572483 (7/6/2012)


    How about create procedure sp_lastdayofyear (@year int) as

    select Convert(date,convert(varchar(4),@year)+'-12-31')

    No real difference between CAST or CONVERT.

    SET NOCOUNT ON;

    DECLARE @MyDate DATE;

    SET STATISTICS TIME ON;

    WITH e1(n) AS (SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d(n)),

    e2(n) AS (SELECT a.n FROM e1 a CROSS JOIN e1 b), -- 100 rows

    e4(n) AS (SELECT a.n FROM e2 a CROSS JOIN e2 b), -- 10,000 rows

    e6(n) AS (SELECT a.n FROM e4 a CROSS JOIN e2 b), -- 1,000,000 rows

    tally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT(NULL))) n FROM e6),

    sampledata AS (SELECT 2000 + FLOOR(10 * rand(CHECKSUM(NEWID()))) AS MyYear FROM tally)

    SELECT

    @MyDate = CAST(CAST(MyYear AS CHAR(4)) + '-12-31' AS DATE)

    FROM

    sampledata;

    SET STATISTICS TIME OFF;

    GO

    DECLARE @MyDate DATE;

    SET STATISTICS TIME ON;

    WITH e1(n) AS (SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d(n)),

    e2(n) AS (SELECT a.n FROM e1 a CROSS JOIN e1 b), -- 100 rows

    e4(n) AS (SELECT a.n FROM e2 a CROSS JOIN e2 b), -- 10,000 rows

    e6(n) AS (SELECT a.n FROM e4 a CROSS JOIN e2 b), -- 1,000,000 rows

    tally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT(NULL))) n FROM e6),

    sampledata AS (SELECT 2000 + FLOOR(10 * rand(CHECKSUM(NEWID()))) AS MyYear FROM tally)

    SELECT

    @MyDate = Convert(date,convert(varchar(4),MyYear)+'-12-31')

    FROM

    sampledata;

    SET STATISTICS TIME OFF;

    GO

    DECLARE @MyDate DATE;

    SET STATISTICS TIME ON;

    WITH e1(n) AS (SELECT n FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) d(n)),

    e2(n) AS (SELECT a.n FROM e1 a CROSS JOIN e1 b), -- 100 rows

    e4(n) AS (SELECT a.n FROM e2 a CROSS JOIN e2 b), -- 10,000 rows

    e6(n) AS (SELECT a.n FROM e4 a CROSS JOIN e2 b), -- 1,000,000 rows

    tally(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT(NULL))) n FROM e6),

    sampledata AS (SELECT 2000 + FLOOR(10 * rand(CHECKSUM(NEWID()))) AS MyYear FROM tally)

    SELECT

    @MyDate = CAST(DATEADD(yy, MyYear - 1899, -1) AS DATE)

    FROM

    sampledata;

    SET STATISTICS TIME OFF;

    GO

    SET NOCOUNT OFF;

  • I've never run into an instance where using CAST over CONVERT (or vice versa) has made a difference in code performance. The problem is usually related to the rest of the code, not the choice of conversion function.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • I need one that will always display the last day of the fiscal year.

  • tim.cloud (7/6/2012)


    I need one that will always display the last day of the fiscal year.

    What is your fiscal year? And do you keep track of it in other ways in your database or do you just happen to know the start month and end month?

    Fiscal year is not consistent across businesses (or even governments). The U.S. Government tends to end their fiscal year in October, for instance, while several S&P 500 companies end theirs in March or June, or even August.

    Define your fiscal year for us and we can give you that code.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Viewing 15 posts - 1 through 15 (of 29 total)

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