Solved

Pulling Month from mixed date format

Posted on 2008-06-24
6
418 Views
Last Modified: 2012-06-21
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

0
Comment
Question by:jimbofish8
6 Comments
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
try this:

select datename(m, cast(yourfieldname as datetime))
from yourtablename
0
 
LVL 16

Expert Comment

by:SQL_SERVER_DBA
Comment Utility
SELECT count(month(columnname)) MonthCount, DATENAME(month, columnname) MonthName FROM Tablename
GROUP BY columnname
0
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
sorry, forgot the aggregate request:

select datename(m, cast(yourfieldname as datetime)), count(*)
from yourtablename
group by datename(m, cast(yourfieldname as datetime))
0
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.

 

Author Comment

by:jimbofish8
Comment Utility
Nice solution. However, i need it in the shortened month form. (ie Mar  instead of March)
0
 
LVL 8

Accepted Solution

by:
CoyotesIT earned 250 total points
Comment Utility
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
 
LVL 60

Assisted Solution

by:chapmandew
chapmandew earned 250 total points
Comment Utility
select left(datename(m, cast(yourfieldname as datetime)), 3), count(*)
from yourtablename
group by datename(m, cast(yourfieldname as datetime))
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.

763 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

10 Experts available now in Live!

Get 1:1 Help Now