How to get one day records? - 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)

How to get one day records?

Author

Topic

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-02-18 : 12:23:50

I used code below to get the same day record but got nothing.
What is wrong?
select * from Order where OrderDate Between '02/06/2009' and '02/06/2009'

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-02-18 : 12:26:37

what is the datatype of the OrderDate column?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 12:28:12

[code]select * from Order where OrderDate >='02/06/2009' and OrderDate<'02/07/2009'[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-18 : 12:29:34

quote:
Originally posted by Sun Foster

I used code below to get the same day record but got nothing.
What is wrong?
select * from Order where OrderDate Between '02/06/2009' and '02/06/2009'

simply '02/06/2009' means its '02/06/2009 00:00:00' which is 12 midnight of the day. you're actually giving start and end as same date value (02/06/2009 00:00:00) thats why it returns nothing

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-02-18 : 15:20:59

select * from Order where OrderDate >='02/06/2009' and OrderDate<'02/07/2009' is working.

The problem is OrderDate is from a calendar control input.
How to change '2/6/2009' to '2/7/2007' ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-18 : 15:56:49

DATEADD(DAY, 1, @Param1)

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-18 : 23:46:38

use like this
where dateadd(dd,datediff(d,0,date),0) = dateadd(dd,datediff(dd,0,getdate()),0)
r indexes
WHERE Date>=DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
AND Date<DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-19 : 00:45:42

Also Try this ,

select * from urtable
where date between DATEADD(dd,DATEDIFF(dd,0,GETDATE()-1),0)
AND DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-19 : 02:09:25

quote:
Originally posted by Nageswar9

Also Try this ,

select * from urtable
where date between DATEADD(dd,DATEDIFF(dd,0,GETDATE()-1),0)
AND DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

Incorrect. See previous replies

Madhivanan

Failing to plan is Planning to fail

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-19 : 02:15:14

OK, is he want between today date and tommorrow's date
or today's Records

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-02-19 : 02:20:57

quote:
Originally posted by Nageswar9

OK, is he want between today date and tommorrow's date
or today's Records

All the records belong to the date selected from the calendar control

Madhivanan

Failing to plan is Planning to fail

Sun Foster
Aged Yak Warrior

515 Posts

Posted - 2009-02-19 : 10:56:13

Thank all of you.
I use code below. It works. I found that "between and" did not give right result not only for the same day issue.

select * from Order where OrderDate >='02/06/2009' and OrderDate<'02/07/2009' is working.

DATEADD(DAY, 1, @Param1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources