Post reply

Get last day of the month - 1

  • October 17, 2013 at 1:56 pm

    #284052

    Hello all,

    Have a quick question....

    How can I get the last day of the month - 1? So if the last day of the month is 10/31/2013, I would need to see 10/30/2013? Also, I do not want time added to the date and I am using SQL 2005.

    Here is what I have...

    select Dateadd(d,-DATEPART(d,DateAdd(m,1,getdate()-1)),DateAdd(m,1,getdate()-1))

    Any help would be appreciated.

  • October 17, 2013 at 1:59 pm

    #1659483

    SELECT DATEADD(s, -1, DATEADD(m, DATEDIFF(m, 0, GETDATE())+1, 0))

    Jonathan Bernardez Bernardez
    ___________________________________________________________
    DBD. MCSA SQL Server 2012

  • October 17, 2013 at 2:01 pm

    #1659484

    I know this is a basic question, but I cannot seem to focus today. Thanks for your time.

  • October 17, 2013 at 2:03 pm

    #1659486

    DaveDB (10/17/2013)

    I know this is a basic question, but I cannot seem to focus today. Thanks for your time.

    Anytime 🙂

    Jonathan Bernardez Bernardez
    ___________________________________________________________
    DBD. MCSA SQL Server 2012

  • October 17, 2013 at 3:29 pm

    #1659512

    Surely you meant:

    SELECT DATEADD(d, -2, DATEADD(m, DATEDIFF(m, 0, GETDATE())+1, 0))

    SELECT DATEADD(s, -1, DATEADD(m, DATEDIFF(m, 0, GETDATE())+1, 0))

    returns: 2013-10-31 23:59:59.000

  • October 17, 2013 at 3:41 pm

    #1659517

    arnipetursson (10/17/2013)

    Surely you meant:

    SELECT DATEADD(d, -2, DATEADD(m, DATEDIFF(m, 0, GETDATE())+1, 0))

    SELECT DATEADD(s, -1, DATEADD(m, DATEDIFF(m, 0, GETDATE())+1, 0))

    returns: 2013-10-31 23:59:59.000

    Indeed. Thanks 🙂

    Jonathan Bernardez Bernardez
    ___________________________________________________________
    DBD. MCSA SQL Server 2012

  • October 17, 2013 at 5:01 pm

    #1659529

    We can cheat this just a little more if you want...

    SELECT DATEADD(dd,-2,DATEADD(mm,DATEDIFF(mm,-1,GETDATE()),0))

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • October 17, 2013 at 11:26 pm

    #1659561

    Select dateadd(dd,-(day(getdate()))-1,getdate())

    or

    Select Dateadd(d,-DATEPART(d,DateAdd(m,1,dateadd(dd,-1,getdate())))-1,DateAdd(m,1,dateadd(dd,-1,getdate())))

    any query will get the lastdayofmonth-1

  • October 18, 2013 at 1:14 am

    #1659575

    Let's say for the sake of argument and amusement you had a really, really fast FUNCTION for generating a calendar table. Like the following:

    CREATE FUNCTION [dbo].[GenerateCalendar]

    (

    @FromDate DATETIME,

    @NoDays INT

    )

    RETURNS TABLE WITH SCHEMABINDING AS

    RETURN

    --===== High speed code provided courtesy of Jeff Moden (idea by Dwain Camps)

    --===== Generate sequence numbers from 1 to 65536 (credit to SQL Guru Itzik Ben-Gen)

    WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1), --2 rows

    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --4 rows

    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --16 rows

    E8(N) AS (SELECT 1 FROM E4 a, E4 b), --256 rows

    E16(N) AS (SELECT 1 FROM E8 a, E8 b), --65536 rows

    cteTally(N) AS (SELECT TOP (ABS(@NoDays)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16)

    SELECT [SeqNo] = t.N,

    [Date] = dt.DT,

    [Year] = dp.YY,

    [YrNN] = dp.YY % 100,

    [YYYYMM] = dp.YY * 100 + dp.MM,

    [BuddhaYr] = dp.YY + 543,

    [Month] = dp.MM,

    [Day] = dp.DD,

    [WkDNo] = DATEPART(dw,dt.DT),

    [WkDName] = CONVERT(NCHAR(9),dp.DW),

    [WkDName2] = CONVERT(NCHAR(2),dp.DW),

    [WkDName3] = CONVERT(NCHAR(3),dp.DW),

    [JulDay] = dp.DY,

    [JulWk] = dp.DY/7+1,

    [WkNo] = dp.DD/7+1,

    [Qtr] = DATEPART(qq,dt.Dt),

    [Last] = (DATEPART(dd,dp.LDtOfMo)-dp.DD)/7+1,

    [LdOfMo] = DATEPART(dd,dp.LDtOfMo),

    [LDtOfMo] = dp.LDtOfMo

    FROM cteTally t

    CROSS APPLY ( --=== Create the date

    SELECT DT = DATEADD(dd,(t.N-1)*SIGN(@NoDays),@FromDate)

    ) dt

    CROSS APPLY ( --=== Create the other parts from the date above using a "cCA"

    -- (Cascading CROSS APPLY, Acourtesy of ChrisM)

    SELECT YY = DATEPART(yy,dt.DT),

    MM = DATEPART(mm,dt.DT),

    DD = DATEPART(dd,dt.DT),

    DW = DATENAME(dw,dt.DT),

    Dy = DATEPART(dy,dt.DT),

    LDtOfMo = DATEADD(mm,DATEDIFF(mm,-1,dt.DT),-1)

    ) dp

    Then the code you need is as simple as:

    SELECT [Last Day of Month -1]=LDtOfMo-1

    FROM dbo.GenerateCalendar(GETDATE(),1);

    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

Viewing 9 posts - 1 through 8 (of 8 total)

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