Retrieving Month value in character format

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 2005 Forums

Transact-SQL (2005)

Retrieving Month value in character format

Author

Topic

c.vaibhav
Starting Member

26 Posts

Posted - 2009-10-21 : 02:13:32

Hi,
I have table with a column Date.

I want to select the month part of those date.

i.e.
my select query must return the following information,

Jan - 2009
Feb - 2009 etc.

I use the following query,

"Select Datepart(mm, InvoiceDate) + ' - ' + Datepart(yyyy, InvoiceDate)"

But this adds the month value and year value.
Can anyone please help.

Thanks & Regards,
Vaibhav

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-21 : 02:30:13

[code]
datename(month, InvoiceDate) + ' - ' + convert(varchar(4), datepart(year, InvoiceDate))
[/code]

KH
[spoiler]Time is always against us[/spoiler]

c.vaibhav
Starting Member

26 Posts

Posted - 2009-10-21 : 02:57:59

thanks a lot!!!!

one more question..

When I do Order By "datename(month, InvoiceDate) + ' - ' + convert(varchar(4), datepart(year, InvoiceDate))"

Then it orders by alphabets.. rather I'll want it to do monthwise..

Thanks..

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-21 : 03:04:23

order by InvoiceDate

KH
[spoiler]Time is always against us[/spoiler]

c.vaibhav
Starting Member

26 Posts

Posted - 2009-10-21 : 03:05:16

I get this error

"Column "Sales.DispatchDatabase.InvoiceDate" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause."

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-21 : 03:13:39

oh .. you have group by . . .

You should be just group by this and let your front end application format into month - year accordingly


select dateadd(month, datediff(month, 0, InvoiceDate), 0), SUM(somecol) as total
from   yourtable
group by dateadd(month, datediff(month, 0, InvoiceDate), 0)

KH
[spoiler]Time is always against us[/spoiler]

c.vaibhav
Starting Member

26 Posts

Posted - 2009-10-21 : 03:18:08

but the data present in my SQL table is not ordered.. there are random entries.. so it is not coming monthwise..

is there any solution to this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-21 : 03:33:51

just use ORDER BY . .


select dateadd(month, datediff(month, 0, InvoiceDate), 0), SUM(somecol) as total
from   yourtable
group by dateadd(month, datediff(month, 0, InvoiceDate), 0)
order by dateadd(month, datediff(month, 0, InvoiceDate), 0)

KH
[spoiler]Time is always against us[/spoiler]

c.vaibhav
Starting Member

26 Posts

Posted - 2009-10-21 : 03:42:02

cool!!!

this is what i wanted..

now it shows all the entries on the 1st date of the month.. but that i can easily take care of..

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-21 : 04:00:13

Yes. A simple formatting on the front end will do the job.

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources