Months not showing - 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)

Months not showing

Author

Topic

Roseli
Starting Member

4 Posts

Posted - 2007-07-06 : 17:18:38

I am trying to get all of the months between 12/2006 and 6/2007 to show in my results - only 12/2006 returns. Help!

SELECT BLC, sum(Headcount),[Cal Year/Month]
FROM table1
WHERE [Cal Year/Month]between '12/2006' and '6/2007'
GROUP BY BLC,[Cal Year/Month]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-06 : 17:34:02

What datatype is [Cal Year/Month]? VARCHAR?
If so, are the data stored YYYY/MM or MM/YYYY?

SELECT BLC, sum(Headcount), [Cal Year/Month]
FROM table1
WHERE [Cal Year/Month]between '2006/12' and '2007/06'
GROUP BY BLC, [Cal Year/Month]

Peter Larsson
Helsingborg, Sweden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-07-06 : 17:36:44

What is the datatype of column [Cal Year/Month]? If it is varchar, I would say that your results are accurate, just not what you want.

What does data in that column look like?

If you run this code, you will see what is happening:
if '12/2006' < '1/2007' print 'yes' else print 'no'
if '2' < '/' print 'yes' else print 'no'

CODO ERGO SUM

Roseli
Starting Member

4 Posts

Posted - 2007-07-06 : 18:15:21

It is nvarchar and formated mm/yyyy. Would changing to vchar make a difference.

DaleJ
Starting Member

7 Posts

Posted - 2007-07-06 : 18:22:29

Try this:

SELECT BLC, sum(Headcount),[Cal Year/Month]
FROM table1
WHERE ((right([Cal Year/Month], 4)*100) + left([Cal Year/Month], 2)) between 200612 and 200706
GROUP BY BLC,[Cal Year/Month]

Though, you won't get the benefit of any index that might be on [Cal Year/Month].

I'd also suggest, if possible on changing it to a datetime field and maybe renaming it...

Roseli
Starting Member

4 Posts

Posted - 2007-07-06 : 18:37:01

thanks Dale that works great!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources