time & n date query

[dave]

hi guys
i m trying to fetch current week's (i.e. today + 6 days
more ) records using sql

sql3="Select * From tblEndMinTerm WHERE DateEnd >= GetDate
() AND DateEnd <= DATEADD(day,6,GetDate()) Order By
DateEnd Desc"

but it doesnt display todays record however it display
data from tomorro's date...

i think its problem with time field ..i need to specify
from 00:00:00 to 23:59:59 with start date and end date..

can any one help me..
thanx..
dave

 

[Ken Schaefer]

WHERE DateEnd >= CAST(CONVERT(GetDate(), varChar, 112) AS SmallDateTime)

etc

Converting to varchar in the specified format, drops the hh:mm:ss, and then
you convert it back to smallDateTime

Cheers
Ken


: hi guys
: i m trying to fetch current week's (i.e. today + 6 days
: more ) records using sql
:
: sql3="Select * From tblEndMinTerm WHERE DateEnd >= GetDate
: () AND DateEnd <= DATEADD(day,6,GetDate()) Order By
: DateEnd Desc"
:
: but it doesnt display todays record however it display
: data from tomorro's date...
:
: i think its problem with time field ..i need to specify
: from 00:00:00 to 23:59:59 with start date and end date..
:
: can any one help me..
: thanx..
: dave

 

[dave]

no not working...
can u suggest me how to put 00:00:00 and 23:59:59 between
start and end date???

thanx
dave

 

[Ken Schaefer]

What do you mean, "not working"?

I made a mistake in the syntax (swap VarChar and GetDate() around), but you
would have found that out if you'd check in SQL Server Books Online.

Cheers
Ken


: no not working...
: can u suggest me how to put 00:00:00 and 23:59:59 between
: start and end date???
:
: thanx
: dave
: >-----Original Message-----
: >WHERE DateEnd >= CAST(CONVERT(GetDate(), varChar, 112)
: AS SmallDateTime)
: >
: >etc
: >
: >Converting to varchar in the specified format, drops the
: hh:mm:ss, and then
: >you convert it back to smallDateTime
: >
: >Cheers
: >Ken
: >
: >
: message
: >: >: hi guys
: >: i m trying to fetch current week's (i.e. today + 6 days
: >: more ) records using sql
: >:
: >: sql3="Select * From tblEndMinTerm WHERE DateEnd >=
: GetDate
: >: () AND DateEnd <= DATEADD(day,6,GetDate()) Order By
: >: DateEnd Desc"
: >:
: >: but it doesnt display todays record however it display
: >: data from tomorro's date...
: >:
: >: i think its problem with time field ..i need to specify
: >: from 00:00:00 to 23:59:59 with start date and end
: date..
: >:
: >: can any one help me..
: >: thanx..
: >: dave
: >
: >
: >.
: >

 

[roger]

no not working...

?


Does this work?

WHERE DateEnd >=

cast(cast(GetDate() as int) as datetime)
AND DateEnd <
cast((cast(GetDate() as int) + 7) as datetime)

Note:
use '<' not '<='
and
add 7 days, not 6

If it doesn't work, please say...

what happened when you tried it
database (I'm guessing SQLServer)
database version
data type of "DateEnd"

 

[Aaron Bertrand - MVP]

can u suggest me how to put 00:00:00 and 23:59:59 between

start and end date???

No, don't do that. See http://www.aspfaq.com/2280