Pulling Month from mixed date format

In my database there are dates which are stored in two different formats (within the same field): MM/DD/YYYY and DD-MMM-YYYY.

I want to extract purely the months from these fields and aggregate based on the consolidated month label.

So, if these are the dates that I have:

03/20/2007 ---> Mar
04/27/2007 ---> Apr
06/03/2007 ---> Jun
1-APR-2007 ---> Apr
11-FEB-2007 ---> Feb
11-MAR-2007 ---> Mar
14-JAN-2007 ----> Jan

jimbofish8Asked:
Who is Participating?
 
CoyotesITConnect With a Mentor Commented:
I would just add then,

select
substring(datename(m, cast(emp_dob as datetime)),1,3) [BirthMonth]
, count(emp_dob)
from employee
group by substring(datename(m, cast(emp_dob as datetime)),1,3)

Tested this on one of my DB's

Good luck!
0
 
chapmandewCommented:
try this:

select datename(m, cast(yourfieldname as datetime))
from yourtablename
0
 
SQL_SERVER_DBACommented:
SELECT count(month(columnname)) MonthCount, DATENAME(month, columnname) MonthName FROM Tablename
GROUP BY columnname
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 
chapmandewCommented:
sorry, forgot the aggregate request:

select datename(m, cast(yourfieldname as datetime)), count(*)
from yourtablename
group by datename(m, cast(yourfieldname as datetime))
0
 
jimbofish8Author Commented:
Nice solution. However, i need it in the shortened month form. (ie Mar  instead of March)
0
 
chapmandewConnect With a Mentor Commented:
select left(datename(m, cast(yourfieldname as datetime)), 3), count(*)
from yourtablename
group by datename(m, cast(yourfieldname as datetime))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.