Solved

Pulling Month from mixed date format

Posted on 2008-06-24
6
419 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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 retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

776 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