June 5, 2012 at 3:44 am
Hi Guys,
Kindly please help me to pullout records using the date.
I would like to pull out this records from 2012/06/04 at 12:00 am in the morning
up to 2012/06/05 at 12:00am also in the morning. using my query, i could not get the exact records.
Any idea. thanks.
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 00:39:43.000')
Select * from #Sample
Where Trandate between @Fromdatetime and @todatetime
derived result form this query.
Trandate
-----------------------
2012-06-04 01:12:00.000
2012-06-04 08:21:59.000
2012-06-04 19:36:59.000
2012-06-04 21:12:00.000
2012-06-04 21:12:00.000
2012-06-04 22:40:31.000
THank you in advance..
J
June 5, 2012 at 4:25 am
Sorry, but I don't see where the problem lies.
Which records did you expect the query to return?
-- Gianluca Sartori
June 5, 2012 at 6:15 am
This was removed by the editor as SPAM
June 5, 2012 at 9:09 am
villanueva.jonel (6/5/2012)
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')
If you only supply a date for a datetime field, the time is defaulted to midnight, so the above is unnecessarily complex. It is equivalent to the following:
Declare @Fromdatetime datetime, @todatetime datetime
SET @FromDateTime = '2012/06/04'
SET @ToDateTime = '2012/06/05'
Also, when working with dates, you generally do NOT want to use BETWEEN, because you are potentially double counting any record that falls right on the border. Generally, you want one end open and the other end closed. For example,
WHERE YourDateField >= @FromDateTime --closed
AND YourDateField < @ToDateTime --open
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 5, 2012 at 9:11 am
I agree too. The query posted matches exactly to the desired results you posted. Excellent job posting ddl, sample data and desired output!!! I wish more people would do that.
You could greatly simplify your datetime declarations if you want. No need for all the dateadd and such. By the time that has happened your strings have already been implicitly converted to a datetime with 00:00.000 as the time portion.
Set @fromdatetime = '2012/06/04'
Set @todatetime = '2012/06/05'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 5, 2012 at 7:10 pm
Thank you guys for the reply.
I have to clear my requirements.
I want to pullout records based on date and time(hour).
In my samples i have date range from 2012/06/04 and 2012/6/5 records.
also have the time.
for 2012/06/04 i need to get only those records that until only 00:00(12:00 am) in the morning
for 2012/06/05 the same, need to pullout only records until 00:00(12:00 am). then i will combine those 2 dates from 2012-06-04 to 2012-06-04. Those records that are past 00:00(12 am) should be excluded.
in my scripts i did not pullout the records that fall from 2012-06-05 00:00 (as 12:00AM) .
its possible to use this kind of approach.
i have to separate the hour instead.
Where trandate >='2012/06/04','12:00AM' and trandate<'2012/6/5','12:00AM'
btw, how i convert the datetime with like this: 2012/06/04 12:00AM
Hope its clear now.
Thanks again.
J
June 6, 2012 at 6:54 am
villanueva.jonel (6/5/2012)
Thank you guys for the reply.I have to clear my requirements.
I want to pullout records based on date and time(hour).
In my samples i have date range from 2012/06/04 and 2012/6/5 records.
also have the time.
for 2012/06/04 i need to get only those records that until only 00:00(12:00 am) in the morning
for 2012/06/05 the same, need to pullout only records until 00:00(12:00 am). then i will combine those 2 dates from 2012-06-04 to 2012-06-04. Those records that are past 00:00(12 am) should be excluded.
in my scripts i did not pullout the records that fall from 2012-06-05 00:00 (as 12:00AM) .
its possible to use this kind of approach.
i have to separate the hour instead.
Where trandate >='2012/06/04','12:00AM' and trandate<'2012/6/5','12:00AM'
btw, how i convert the datetime with like this: 2012/06/04 12:00AM
Hope its clear now.
Thanks again.
J
Not sure how looking at the hour of 12am is any different. The time portion of that is 00:00:00.000.
To answer your question about adding time to your datetime variable you just need to add it.
Set @fromdatetime = '2012/06/04 20:22:44.737'
--Note-- This format will work based on your previous posts but will not work in all instances of sql server due to date formatting.
Please notice that the hour is a 24 hour value.
Hope that helps gets you closer.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply