Getting month and year - SQL Server Forums

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)

Getting month and year

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-05-29 : 14:49:00

How do I get it with this query?

Select distinct (convert(char,(dowrdat),100)) as bgndt
from dbo.ClearedDiary
order by bgndt

Right now I'm getting
Apr 24 2009 (from the data in the table)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-29 : 14:50:53

See http://weblogs.sqlteam.com/peterl/archive/2009/03/18/A-general-approach-to-sort-different-dateformats-correctly.aspx

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-29 : 14:54:36

quote:
Originally posted by JJ297

How do I get it with this query?

Select distinct (convert(char,(dowrdat),100)) as bgndt
from dbo.ClearedDiary
order by bgndt

Right now I'm getting
Apr 24 2009 (from the data in the table)

why are converting date to varchar?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-29 : 14:59:43

Maybe for a combobox at the client?
Format one way but still sort and order by another way?

E 12°55'05.63"
N 56°04'39.26"

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-05-29 : 15:01:38

I guess I don't need to covert to Varchar

Peso sorry I don't understand how to convert the date by looking at what you sent

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-05-29 : 15:12:53

Yes that's it Peso. Sorry I left that out. I do have a dropdown box that needs to display month and year only. In the database I have 4/24/2009

I am trying to get a count using this query. This works but I need my drop down to reflect month and year only

SELECT COUNT(weekdat) AS Expr1
FROM ClearedDiary
WHERE (weekdat BETWEEN '4/17/2009' AND '5/15/2009 ')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-29 : 15:21:11

You mean the example in the link above is too hard to understand?

Select convert(char(7), dowrdat, 120) as bgndt,
count(*)
from dbo.ClearedDiary
group by convert(char(7), dowrdat, 120)
order by convert(char(7), dowrdat, 120) desc

E 12°55'05.63"
N 56°04'39.26"

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-05-29 : 15:33:15

Yes

Thanks!

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-05-29 : 15:37:41

One more question how do I put the month in front of the year it is reading 2009-05

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-29 : 15:47:36

You should really read the blog post linked to above...

SELECT		CONVERT(CHAR(2), dowrdat, 101) + '-' + DATENAME(YEAR, dowrdat) AS theDate,
		COUNT(*)
FROM		dbo.ClearedDiary 
GROUP BY	CONVERT(CHAR(2), dowrdat, 101) + '-' + DATENAME(YEAR, dowrdat),
		CONVERT(CHAR(7), dowrdat, 120)
ORDER BY	CONVERT(CHAR(7), dowrdat, 120) DESC

E 12°55'05.63"
N 56°04'39.26"

JJ297
Aged Yak Warrior

940 Posts

Posted - 2009-06-01 : 08:55:58

Thanks Peso. Took it home over the weekend and read through it and now understand it!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-01 : 09:22:23

Great!
Good luck and thank your for the feedback.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources