Cast 2 ints as Date - 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)

Cast 2 ints as Date

Author

Topic

boggyboy
Yak Posting Veteran

57 Posts

Posted - 2010-03-15 : 17:05:16

I have a table that looks like this...

MonthNo, YearNo, UserID, LoggedInBit
12,2009,345-22-3412,true
12,2009, 555-55-5555, true
1,2010,345-22-3412,true
1,2010, 555-55-5555, true

Both MonthNo and YearNo are integers

My query does not sort correctly (I want to sort by date ascending)

select cast(MonthNo as nvarchar(10)) + '/' + cast(yearNo as nvarchar(10)) as _date
,Sum(cast(LoggedINbit as int)) as no_Logins
from LoginHistory
group by cast(MonthNo as nvarchar(10)) + '/' + cast(yearNo as nvarchar(10))
order by cast(MonthNo as nvarchar(10)) + '/' + cast(yearNo as nvarchar(10)) asc

Nick W Saban

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-15 : 17:08:03

Why don't you convert it to datetime to get it to sort properly? You'll just need to concatenate in the day to form a proper date.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-15 : 17:09:15

You should really consider changing your database schema so that it uses datetime. Perhaps keep your two columns if you need those two items frequently, and then add an additional column that is datetime so that queries like you have in this post are simple. Datetime data type will perform faster too for queries like this.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-15 : 17:16:17

No need to convert to string (or date) for sorting is there?

select cast(MonthNo as nvarchar(10)) + '/' + cast(yearNo as nvarchar(10)) as _date
,Sum(cast(LoggedINbit as int)) as no_Logins
from LoginHistory 
group by yearNo, MonthNo 
order by yearNo, MonthNo

boggyboy
Yak Posting Veteran

57 Posts

Posted - 2010-03-15 : 17:32:17

Pretty simple but That did the trick!! thank you Very much.

Nick W Saban

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources