Date parameters.... - 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)

Date parameters....

Author

Topic

petek
Posting Yak Master

192 Posts

Posted - 2009-10-08 : 04:22:33

hi,

can somebody help me with a Date problem.

I need two input parameters where users input a startdate and enddate but i want the dates
to be a the whole day.

select top 100 * from table1
where startdate >= '2004-08-30'
and enddate <= '2004-8-31'

so looking at the above example it needs to be from midnight 30th to 23:59:59 on 31st

i can add the date to the select but i dont want users to do this; they just need to add the
two days.

Kind Regards

Pete.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-08 : 04:35:38

[code]
where startdate >= @startdate
and enddate < dateadd(day, 1, @enddate)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

petek
Posting Yak Master

192 Posts

Posted - 2009-10-08 : 05:37:14

Hi khtan

I have tried the code but the results return a different row count to when i use
the whole string.

DECLARE @StartDateTime Datetime
DECLARE @EndDateTime Datetime

SET @StartDateTime = GETDATE ()-2
SET @EndDateTime = GETDATE ()-1

select * from dbo.table1
where Datestamp >= @StartDateTime
and Datestamp < dateadd(day, 1, @EndDateTime)
order by 5

474 rows

the includes some results from todays date???

select * from dbo.ticket
where statusdate >= '2009-10-06 00:00:00.000'
and statusdate <= '2009-10-07 23:59:59.999'
order by 5

463 rows

this is just between the two dates

not sure if i was clear earlier.......user inputs two parameters:
startdate (2009-10-06)
enddate (2009-10-07)

i want all results between these days buit i dont want users to input the time.....

i needs to bring results from start midnight to midnight end date

Kind Regards

Pete.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-10-08 : 05:53:57

if you input paramter contains time, then you need to remove it using dateadd(day, datediff(day, 0, [date]), 0)

You get diff result from specify the date alone cause getdate() will give you date & time.

use this . .


DECLARE @StartDateTime Datetime
DECLARE @EndDateTime Datetime

SET @StartDateTime = GETDATE ()-2
SET @EndDateTime = GETDATE ()-1

select * from dbo.table1
where Datestamp >= dateadd(day, datediff(day, 0, @StartDateTime), 0)
and   Datestamp <  dateadd(day, datediff(day, 0, @EndDateTime),   1)
order by 5

KH
[spoiler]Time is always against us[/spoiler]

petek
Posting Yak Master

192 Posts

Posted - 2009-10-08 : 06:16:01

thanks Khtan........thats brilliant!!

Kind Regards

Pete.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources