Easiest query to find start of month

  • Hi Guys

    What will be easiest way without using variables to get first date of month from a date.

    I got someting like:

    Select Convert(Datetime, Cast(DatePart(mm,GetDATE()) as

    Varchar) + '/1/' + Cast(DatePart(yy,GetDATE()) as Varchar),101)

    Does anybody have something better?

    Brij

  • Brij

    Try this.  It will return a date whose time component is the same as the date you specify.  If you want it to return a date that ends with 00:00:00.000 then you can apply a similar logic to the time component.

    select dateadd (d, 1 - datepart(d, getdate()), getdate())

    John

  • Thanks John

    Yes that is correct I need time component as 00:00:00.000

    Brij

  • select

    dateadd (month, datediff(month, 0, getdate()), 0)

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • Is that automatically color-coded or you do do it by hand?

    I'd like to know, because some examples I give here needs color-coding.


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

  • use SQL2005 SSMS and you'll find it very usefull.

    copy/past keeps the colors

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Yes that's it! You still have to format as definition term, courier new, size 2 though.

    And of course, always <select all>,<copy> before clicking the button, in case of session timeout!

    But I'm teaching my grandmother to suck eggs now.

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

  • Is she good at it?

    --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)

  • The last time I had to do it, I actually blew the egg rather than sucked it. It was a swan's egg that was highly addled. One drills a hole at both ends and blows from the top end. Had I sucked it, I'd have had a mouth-full of addled egg. We really need the advice of an egg-collector.

    SELECT CONVERT(DateTime,'01 '+SUBSTRING(CONVERT(CHAR(11), GETDATE(),113),4,8))

    Best wishes,
    Phil Factor

  • I have advice for egg collectors. Stop killing off rare species.

    For those who don't know, the saying is an old one, and refers to sucking eggs for food, a basic skill which (presumably) any grandmother could be expected to have already acquired.

    My own grandmothers as far as I know eat their  eggs (yes, free-range and organic) in the form of an omelette. I suppose they could blow the eggs in order to do so, thus disproving another old saying...

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

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

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