[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

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

How can format this date field into yyyy-mm?
I am doing monthly report which counts items by date field.
Example:
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??

0
dkim18
Asked:
dkim18
  • 3
  • 2
2 Solutions
 
dbbishopCommented:
CONVERT(CHAR(7), myDate, 121)
0
 
dkim18Author Commented:
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

0
 
dbbishopCommented:
Should be okay. Are you stating:
 WHERE myDate IS NOT NULL
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
dkim18Author Commented:
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.

0
 
dbbishopCommented:
SUM(CASE WHEN Item = 'A' THEN 1 ELSE 0 END) AS ItemCount
0
 
Marcel HopmanCommented:

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now