Group by 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 2000 Forums

Transact-SQL (2000)

Group by date

Author

Topic

tc
Starting Member

11 Posts

Posted - 2005-07-15 : 10:54:58

I'm new to sql, but this seems like it should be fairly simple. The following works:

$Day = "2005-07-12";

$sql = 'SELECT convert(varchar(10), DateTime, 101) as Date,
Pot_No
FROM dbo.Objects INNER JOIN dbo.Events ON dbo.Objects.Code = dbo.Events.object
WHERE object=369 and Pot_No < 44 and
datetime >= \'' . date('d-M-Y',strtotime($Day)) . '\'
GROUP BY convert(varchar(10), DateTime, 101), Pot_No
ORDER BY Date';

If I use the following instead, I don't get any results because its looking at the time field (I believe):
datetime = \'' . date('d-M-Y',strtotime($Day)) . '\'

I want to make it just = to a specific date. How do I make my where clause ignore the time field, or am I going about this all wrong? Any help will be greatly appreciated.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-15 : 15:55:42

One way is to use sql's dateDiff function. Use difference in days = 0.
ie:


declare @dtColumn datetime
	,@DateWithoutTime dateTime
select	@dtColumn = getdate() --date with time
	,@DateWithoutTime = dateadd(day, datediff(day,0,getdate()), 0) --same date without time

select	@dtColumn, @DateWithoutTime
where	datediff(day, @dtColumn, @DateWithoutTime) = 0

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-07-15 : 15:57:46

On a different topic, you should consider calling parameterized stored procedures instead of application generated in-line sql.

Be One with the Optimizer
TG

MichaelP
Jedi Yak

2489 Posts

Posted - 2005-07-15 : 16:10:56

This looks like Oracle....

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources