HOw to format date in SQL server ('yyyy-mm')

Posted on 2007-10-11
Last Modified: 2008-01-09
How can format this date field into yyyy-mm?
I am doing monthly report which counts items by date field.
ID  DateReceived Items Status
2   2002-10-20       3      active
3   2006-02-22      4        inactive

From this I do monthly counting and get something like this:
Month   TotalItem   Totalactive TotalInactive
2002-02    20             3                4
2006-10    33            3                   3

The reason I use this 'yyyy-mm' is to sort them in montly order.

I already have working sql for the excel/access but I migrate the data to the sql server now.
I used this format(DateReceived, 'yyyy-mm') but sql doesn't allow this function.

I tried this,
convert(varchar(4),month()) + '-' + convert(varchar(2),month())
but this give 2002-2....instead of one i need like 2002-02.

I also tried Left(Datereceived, 7) but this gave out October 20 instead of 2007-10.

Any idea??

Question by:dkim18
    LVL 15

    Expert Comment

    CONVERT(CHAR(7), myDate, 121)

    Author Comment

    Found it.
    CONVERT(CHAR(7), myDate, 120)

    Anyway, do you know if IS NOT NULL can be used in sql server 2000??
    I think it is OK in sql server 2005 but I am getting an error from 2000

    LVL 15

    Expert Comment

    Should be okay. Are you stating:

    Author Comment

    I was getting errors from my excel sql but I think IS Not Null is ok.
    I have something like this:

    Sum(IIF(Item='a',1,0)) as ItemCount.

    How can I change this so it would work in SQL server?

    I tried to use the case but didn't quite make it working.

    LVL 15

    Accepted Solution

    SUM(CASE WHEN Item = 'A' THEN 1 ELSE 0 END) AS ItemCount
    LVL 6

    Assisted Solution


    LEFT(CONVERT(VARCHAR(10), Datereceived, 121),7) -- Result yyyy-mm

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now