Post reply

How To return Year & Month in this format: 2013-06

  • June 19, 2013 at 4:57 am

    #295464

    Hi All,

    I need to return the minimum date from my table and manipulate it so it starts from the first of the month

    e.g 2012-06+'-'+'01+ and use this as the minimum date: '2012-06-01- instead of the 22nd

    Select MIN(dtePostedToWebsiteDate) --Here's the minimum date >>> 2012-06-22 09:07:42.413

    FROM dtlVacancyPostAudit

    How do I achieve this?

    Thanks

    Teee

  • June 19, 2013 at 5:06 am

    #1625384

    Teee (6/19/2013)

    Hi All,

    I need to return the minimum date from my table and manipulate it so it starts from the first of the month

    e.g 2012-06+'-'+'01+ and use this as the minimum date: '2012-06-01- instead of the 22nd

    Select MIN(dtePostedToWebsiteDate) --Here's the minimum date >>> 2012-06-22 09:07:42.413

    FROM dtlVacancyPostAudit

    How do I achieve this?

    Thanks

    Teee

    Select DATEADD(MONTH, DATEDIFF(MONTH, 0, MIN(dtePostedToWebsiteDate)), 0)

    FROM dtlVacancyPostAudit;

    Or

    Select CAST(DATEADD(MONTH, DATEDIFF(MONTH, 0, MIN(dtePostedToWebsiteDate)), 0) AS DATE)

    FROM dtlVacancyPostAudit;

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • June 19, 2013 at 5:15 am

    #1625386

    Thank you so much, this works perfectly 🙂

  • June 20, 2013 at 9:44 am

    #1625991

    I like that answer, thanks for sharing. I know some may find that logic hard to follow, at least some that I know. 🙂 We have always used the following code to get the first of the month. Of course you would replace the getdate() with the date from your table.

    select DATEADD(dd, -(DATEPART(dd, GETDATE())) +1, convert(char(10), GETDATE(), 101))

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

Viewing 4 posts - 1 through 3 (of 3 total)

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