Counting records based on date

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)

Counting records based on date

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2011-06-08 : 15:23:22

Greetings experts,

I have a dateTime field called decisionDate.

I also have thousands of records on the db with decisionDate starting as far back as Jan 1999 and all the way to 2011.

Right now, we are trying to get a count of records where the decisionDate is on or after January 2009.

Any help with this query?

The key thing there is on or after January 2009.

The value for the datetime fieldname follows the following format:

Month Name Day Year HH:MM:SS AM or PM

Example:
Jul 28 2004
5:00:00 PM

Any idea how to put this in the WHERE clause?

Thanks a lot in advance

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-08 : 20:43:10

[code]select * from myTable
where
cast(
cast(Year as varchar(4))
+
case
when [Month Name] = 'Jan' then '01'
when [Month Name] = 'Feb' then '02'
when [Month Name] = 'Mar' then '03'
when [Month Name] = 'Apr' then '04'
when [Month Name] = 'May' then '05'
when [Month Name] = 'Jun' then '06'
when [Month Name] = 'Jul' then '07'
when [Month Name] = 'Aug' then '08'
when [Month Name] = 'Sep' then '09'
when [Month Name] = 'Oct' then '10'
when [Month Name] = 'Nov' then '11'
when [Month Name] = 'Dec' then '12'
end
+
right('0'+cast([Day] as varchar(2)),2)
as datetime) >= '20090101';[/code]If you have only a few thousand records, this may be ok, but otherwise, this is likely to be terribly slow.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-08 : 21:14:45

what is the data type for decisionDate ?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources