Date conversion for month and year together

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Date conversion for month and year together

Author

Topic

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-08-30 : 12:13:38

Hi- I'm trying to group by month and year of a date column. I'm not sure how to do it in sql server, I know the oracle way, and I'm on SQL Server 2000.
Something like this would be great, but I need the increment to be MMYY not the entire day. I don't see a code for what I'm looking for so maybe I need a different function.

select CONVERT(VARCHAR, getdate(),101) from vwProcedures

What I'm trying to do is
select CONVERT(VARCHAR, getdate(),'mm-yyyy'), count(*) as totalpermonth from vwProcedures
group by CONVERT(VARCHAR, getdate(),'mm-yyyy')
(this is not an option with convert, but just as a demonstration)

THanks

Kristen
Test

22859 Posts

Posted - 2005-08-30 : 12:23:08

The way I do this is to convert the actual date (and time if any) to the 1st of the month (midnight), and then group by that - so I get a valid date column to sort by etc. There are various ways to convert a date to 1st of the Month, the one I use is a bit complicated to read, but is the fastest I know of:


SELECT DateAdd(Day, DateDiff(Day, 0, MyDateColumn), 0) AS [Date],
       COUNT(*) AS totalpermonth 
FROM vwProcedures
GROUP BY DateAdd(Day, DateDiff(Day, 0, MyDateColumn), 0)
ORDER BY [Date]

Kristen

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-08-30 : 12:30:18

I tried that but I don't totally understand my results..

Date totalpermonth
2004-08-02 00:00:00.000 1
2003-03-31 00:00:00.000 4
2003-03-29 00:00:00.000 1
2003-03-28 00:00:00.000 2
2003-03-27 00:00:00.000 6
2003-03-26 00:00:00.000 1

If the decond column is month, shouldn't I only have one entry per month? Thanks

Nicki

Kristen
Test

22859 Posts

Posted - 2005-08-30 : 23:21:23

Sorry, I wasn't concentrating! I rounded the date to "day" not "month".

I'll try again!


SELECT DateAdd(Month, DateDiff(Month, 0, MyDateColumn), 0) AS [Date],
       COUNT(*) AS totalpermonth 
FROM vwProcedures
GROUP BY DateAdd(Month, DateDiff(Month, 0, MyDateColumn), 0)
ORDER BY [Date]

Kristen

nicki_assumption
Yak Posting Veteran

69 Posts

Posted - 2005-08-31 : 12:07:54

Great- thanks a bunch!!

neuron
Starting Member

1 Post

Posted - 2005-09-20 : 09:19:10

Thanks a lot! This is what I was searching for.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources