search 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)

search date

Author

Topic

renu
Starting Member

47 Posts

Posted - 2008-10-30 : 01:24:28

how do i search date using like operator in sql 2005?

karthickbabu
Posting Yak Master

151 Posts

Posted - 2008-10-30 : 02:00:47

You can easily get the data using Range Operator

SELECT * FROM TableName WHERE From_Date BETWEEN '2008-10-09' AND '2008-10-30'

================================================

When you realize you've made a mistake, take immediate steps to correct it.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-30 : 03:24:32

quote:
Originally posted by renu

how do i search date using like operator in sql 2005?

Can you be more specific on what you are trying to do?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 03:43:43

and if your attempt is to select records belonging to particular day after ignoring time part do this

SELECT * FROM TableName WHERE From_Date > DATEADD(dd,DATEDIFF(dd,0,@DateValue),0)
AND From_Date < DATEADD(dd,DATEDIFF(dd,0,@DateValue)+1,0)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-30 : 04:59:09

quote:
Originally posted by visakh16

and if your attempt is to select records belonging to particular day after ignoring time part do this

SELECT * FROM TableName WHERE From_Date >= DATEADD(dd,DATEDIFF(dd,0,@DateValue),0)
AND From_Date < DATEADD(dd,DATEDIFF(dd,0,@DateValue)+1,0)

Madhivanan

Failing to plan is Planning to fail

andros30
Yak Posting Veteran

80 Posts

Posted - 2008-10-30 : 11:47:52

I used to use BETWEEN until I found it returned incomplete data on one of my projects. I ran the report using BETWEEN '09/01/08' AND '09/30/08' and I was missing some numbers. However, when I used >= '09/01/08' AND < '10/01/08' then my numbers match up. I found that the BETWEEN statement did not include numbers from 9/30/08 in my report.

Can anyone explain this?

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2008-10-30 : 11:52:50

you should have used

BETWEEN '09/01/08' AND '09/30/08 23:59:59'

instead of

BETWEEN '09/01/08' AND '09/30/08 00:00:00'

cognos79
Posting Yak Master

241 Posts

Posted - 2008-10-30 : 13:12:17

my first gut would be...its also taking time stamp into consideration..so convert your database column to takeout timestamp...something like this

cast(convert(varchar(10),dbfield,101) as datetime) between '09/01/08' AND '10/01/08'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-30 : 13:20:05

quote:
Originally posted by cognos79

my first gut would be...its also taking time stamp into consideration..so convert your database column to takeout timestamp...something like this

cast(convert(varchar(10),dbfield,101) as datetime) between '09/01/08' AND '10/01/08'

no need of converting to varchar for striping time just use DATEADD(dd,DATEDIFF(dd,0,dbfield),0)

but if you've an index on dbfield then its better to use Madhi's suggestions as using the above method causes index to be ignored.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-31 : 02:28:41

quote:
Originally posted by rohitkumar

you should have used

BETWEEN '09/01/08' AND '09/30/08 23:59:59'

instead of

BETWEEN '09/01/08' AND '09/30/08 00:00:00'

That is not reliable
best approach is

datecol>= '09/01/08' AND datecol< '10/01/08'

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources