If possible need a more elegant solution to a current brute force view

  • Jeff Moden

    SSC Guru

    Points: 995168

    Much like Des Norton, I don't see the need for CASE in this case (pun intended).

    --===== Create a table of random PeriodID's.
    -- This is NOT a part of the solution. We're just building test data here.
    DROP TABLE #TestTable
    SELECT TOP 1000000
    PeriodID = DATENAME(mm,DATEADD(dd,ABS(CHECKSUM(NEWID())%365),'2019'))
    INTO #TestTable
    FROM sys.all_columns pc1
    CROSS JOIN sys.all_columns pc2
    ;
    --===== Demo one CASE-less solution.
    -- The year comes from whatever year GETDATE() returns. You can change that to a variable if needed.
    -- This works on SS 2005 and up and does NOT require anything special for Leap Years
    -- If the target can handle dates with midnight times, remove the CONVERT for a bit of extra performance.
    SELECT PeriodID
    ,EOMDate = CONVERT(CHAR(10),DATEADD(dd,-1,DATEADD(mm,1,PeriodID + DATENAME(yy,GETDATE()))),101)
    FROM #TestTable
    ;
    --===== This does the same thing for SS 2012 and up.
    SELECT PeriodID
    , ConvertedToEOM = EOMONTH(CONVERT(DATE,PeriodID+' '+ DATENAME(yy,GETDATE())))
    FROM #TestTable
    ;

    And, yeah... the screwball concatenation with no day and no spacing works just fine.  For example...

    SELECT CONVERT(DATETIME,'January2019');

    ... returns 2019-01-01 00:00:00.000

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • DesNorton

    SSC-Insane

    Points: 22855

    Jeff Moden wrote:

    DesNorton wrote:

    You can also use the following, which will take care of teh leap year

    DECLARE @Year int = 2019;

    SELECT PeriodID, EOMONTH(PeriodID + ' 01 ' + CONVERT(VARCHAR(4), @Year)) AS PeriodEndDate

    True enough but this is a 2008 forum.

     

    Oh snap.

     

    Note to self.  Don't answer posts as 02h30

  • randyetheridge

    SSCommitted

    Points: 1697

    ok thanks for all the posts.  First this is a view, and I now know declare statements do not work in a view

    so I updated the statement to this, but it gives the date as 2019-04-30 00:00:00:000

    however I need 04/30/2019

    Can someone update my SQL to accomplish this?

    Case

    When PeriodID = 'January' THEN Dateadd(Day,-1,Dateadd(Month,1, Dateadd(Year,Datediff(Year,0,Getdate()),0)))

    When PeriodID = 'February' THEN Dateadd(Day,-1,Dateadd(Month,2, Dateadd(Year,Datediff(Year,0,Getdate()),0)))

    When PeriodID = 'March' THEN Dateadd(Day,-1,Dateadd(Month,3, Dateadd(Year,Datediff(Year,0,Getdate()),0)))

    When PeriodID = 'April' THEN Dateadd(Day,-1,Dateadd(Month,4, Dateadd(Year,Datediff(Year,0,Getdate()),0)))

    When PeriodID = 'May' THEN Dateadd(Day,-1,Dateadd(Month,5, Dateadd(Year,Datediff(Year,0,Getdate()),0)))

    When PeriodID = 'June'  THEN Dateadd(Day,-1,Dateadd(Month,6, Dateadd(Year,Datediff(Year,0,Getdate()),0)))

    When PeriodID = 'July'  THEN Dateadd(Day,-1,Dateadd(Month,7, Dateadd(Year,Datediff(Year,0,Getdate()),0)))

    When PeriodID = 'August'  THEN Dateadd(Day,-1,Dateadd(Month,8, Dateadd(Year,Datediff(Year,0,Getdate()),0)))

    When PeriodID = 'September'  THEN Dateadd(Day,-1,Dateadd(Month,9, Dateadd(Year,Datediff(Year,0,Getdate()),0)))

    When PeriodID = 'October'  THEN Dateadd(Day,-1,Dateadd(Month,10, Dateadd(Year,Datediff(Year,0,Getdate()),0)))

    When PeriodID = 'November'  THEN Dateadd(Day,-1,Dateadd(Month,11, Dateadd(Year,Datediff(Year,0,Getdate()),0)))

    Else Dateadd(Day,-1,Dateadd(Month,12, Dateadd(Year,Datediff(Year,0,Getdate()),0))) END

    AS PeriodEndDate,

  • rVadim

    Hall of Fame

    Points: 3938

    Use Convert function. It's in Jeff's example as well.

    CONVERT(CHAR(10), <your date here>, 101)

    --Vadim R.

  • DesNorton

    SSC-Insane

    Points: 22855

    There is no need for the case statement.

     

    This should do the trick

    SELECT CONVERT(VARCHAR(10), DATEADD(DAY, -1, DATEADD(MONTH, 1, 'February' + ' 01 ' + CONVERT(VARCHAR(4), YEAR(GETDATE())))), 101) AS PeriodEndDate
  • Jeff Moden

    SSC Guru

    Points: 995168

    randyetheridge wrote:

    ok thanks for all the posts.  First this is a view, and I now know declare statements do not work in a view so I updated the statement to this, but it gives the date as 2019-04-30 00:00:00:000 however I need 04/30/2019 Can someone update my SQL to accomplish this? Case When PeriodID = 'January' THEN Dateadd(Day,-1,Dateadd(Month,1, Dateadd(Year,Datediff(Year,0,Getdate()),0))) When PeriodID = 'February' THEN Dateadd(Day,-1,Dateadd(Month,2, Dateadd(Year,Datediff(Year,0,Getdate()),0))) When PeriodID = 'March' THEN Dateadd(Day,-1,Dateadd(Month,3, Dateadd(Year,Datediff(Year,0,Getdate()),0))) When PeriodID = 'April' THEN Dateadd(Day,-1,Dateadd(Month,4, Dateadd(Year,Datediff(Year,0,Getdate()),0))) When PeriodID = 'May' THEN Dateadd(Day,-1,Dateadd(Month,5, Dateadd(Year,Datediff(Year,0,Getdate()),0))) When PeriodID = 'June'  THEN Dateadd(Day,-1,Dateadd(Month,6, Dateadd(Year,Datediff(Year,0,Getdate()),0))) When PeriodID = 'July'  THEN Dateadd(Day,-1,Dateadd(Month,7, Dateadd(Year,Datediff(Year,0,Getdate()),0))) When PeriodID = 'August'  THEN Dateadd(Day,-1,Dateadd(Month,8, Dateadd(Year,Datediff(Year,0,Getdate()),0))) When PeriodID = 'September'  THEN Dateadd(Day,-1,Dateadd(Month,9, Dateadd(Year,Datediff(Year,0,Getdate()),0))) When PeriodID = 'October'  THEN Dateadd(Day,-1,Dateadd(Month,10, Dateadd(Year,Datediff(Year,0,Getdate()),0))) When PeriodID = 'November'  THEN Dateadd(Day,-1,Dateadd(Month,11, Dateadd(Year,Datediff(Year,0,Getdate()),0))) Else Dateadd(Day,-1,Dateadd(Month,12, Dateadd(Year,Datediff(Year,0,Getdate()),0))) END AS PeriodEndDate,

    Again, there is no need for the CASE statement.  See my previous post.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • randyetheridge

    SSCommitted

    Points: 1697

    got it thanks

  • randyetheridge

    SSCommitted

    Points: 1697

    Again many thanks for all your help.  this was the final solution

     

    CONVERT(CHAR(10), Dateadd(dd,-1, Dateadd(mm,1,PeriodID + DateName(yy,Getdate()))), 101) AS PeriodEndDate

  • Jeff Moden

    SSC Guru

    Points: 995168

    Sorry... duplicate post removed.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Jeff Moden

    SSC Guru

    Points: 995168

    Thanks for taking the time to post back, Randy.  Since you're one of the folks that needs to support it, do you know how it works?

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 16 through 25 (of 25 total)

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