Selecting data part of time - 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)

Selecting data part of time

Author

Topic

skybvi
Posting Yak Master

193 Posts

Posted - 2012-02-02 : 15:18:47

Hi,
I am having data in one of the columns(DT) in a table as datetime
2012-02-01 08:32:19.810
2012-02-01 15:32:19.810
However i want to see all of the data of that particular day
(2012-02-01)

So iam querying

select upc,desc,DT
from PLU
where SUBSTRING(DT,1,10) = substring((GETDATE()-1),1,10)

I am getting error :-
The data type datetime is invalid for the substring function. Allowed types are: char/varchar, nchar/nvarchar, and binary/varbinary.

Regards,
Sushant
DBA
Virgin Islands(U.K)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-02 : 15:49:10

Since the data is stored as datetime data type, it is much better to simply compare days, for example like this:

WHERE
	DAY(GETDATE()) = DAY(DT)

What would be even better would be to do something like below, both because this may perform better and because if your data spans more than one month, it won't pick up data from other months that have the same date.

WHERE
	DT >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0) 
	AND DT < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)

skybvi
Posting Yak Master

193 Posts

Posted - 2012-02-02 : 16:06:52

Thanks dear.

Regards,
Sushant
DBA
Virgin Islands(U.K)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources