Solved

Pulling Month from mixed date format

Posted on 2008-06-24
6
424 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 60

Expert Comment

by:chapmandew
ID: 21856802
try this:

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

Expert Comment

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

Expert Comment

by:chapmandew
ID: 21856847
sorry, forgot the aggregate request:

select datename(m, cast(yourfieldname as datetime)), count(*)
from yourtablename
group by datename(m, cast(yourfieldname as datetime))
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

Author Comment

by:jimbofish8
ID: 21857217
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
ID: 21857517
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
ID: 21857634
select left(datename(m, cast(yourfieldname as datetime)), 3), count(*)
from yourtablename
group by datename(m, cast(yourfieldname as datetime))
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

732 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