Hi Guys,

Here is my requirements:
I need to pullout records based on datetime fields ranges from 12 am in the morning of 2012/6/4 and 
    12 am in the morning of 2012/06/05.
kindly please check my declare parameter for date if it is correct.    Thank you in advance.

Data:
Declare @Fromdatetime datetime, @todatetime datetime
Set @fromdatetime =dateadd(day,datediff(day,0,'2012/06/04'),'00:00')
Set @todatetime=dateadd(day,datediff(day,0,'2012/06/05'),'00:00')

Create table #Sample
(Trandate datetime)
Insert #sample (trandate) values ('2012-06-04 01:12:00.000')
Insert #sample (trandate) values ('2012-06-05 19:36:17.000')
Insert #sample (trandate) values ('2012-06-04 08:21:59.000')
Insert #sample (trandate) values ('2012-06-05 00:29:31.000')
Insert #sample (trandate) values ('2012-06-04 19:36:59.000')
Insert #sample (trandate) values ('2012-06-04 21:12:00.000')
Insert #sample (trandate) values ('2012-06-05 16:36:17.000')
Insert #sample (trandate) values ('2012-06-04 21:12:00.000')
Insert #sample (trandate) values ('2012-06-04 22:40:31.000')
Insert #sample (trandate) values ('2012-06-05 11:39:43.000')
Insert #sample (trandate) values ('2012-06-05 13:39:43.000')

My Query:
Select * from #Sample
Where Trandate between @Fromdatetime and @todatetime


Thanks.
J

Recommended Answers

All 3 Replies

Set @fromdatetime =dateadd(day,datediff(day,0,'2012/06/04'),'00:00')
Set @todatetime=dateadd(day,datediff(day,0,'2012/06/05'),'00:00')

The declaration looks fine but for the instantiation you could remove the dateadd functions and use a format that can be converted to express minutes/seconds. I think this would work a smidge more efficiently.

DECLARE @fromdatetime DATETIME, @todatetime DATETIME

SELECT @fromdatetime = '2012/06/04 00:00', @todatetime= '2012/06/05 00:00'

--check results
SELECT @fromdatetime, @todatetime

hi
i need something like that but for my report a client has to get info according to the dates he/she selected. i have a @fromdate and @todate parameters but i don't know how to define them, they return headings, please help guys am new on sql am still trying to figure tings out

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.