sql queery
-
Please tell me sql queery which returns the recordset between two date inclusive of two dates
-
Please tell me sql queery which returns the recordset between two date inclusive of two dates
-
Please tell me sql queery which returns the recordset between two date inclusive of two dates
sonia_basangar wrote:
Please tell me sql queery which returns the recordset between two date inclusive of two dates
You have not provided enough information to do that. However, I can tell you that in your WHERE clause you will need something that looks like this:
MyDateColumn >= @SomeStartDate AND MyDateColumn <= @SomeEndDate
Remember that for the end date, you may have to set the time element to 23:59 if you are storing a date and time and want the results for any time on the end date.
Upcoming events: * Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
-
"select * from from table where coldate between date1 and date2" date1 and date2 should be according to database format.
Regards Shajeel
I always avoid BETWEEN because it is not clear whether it is inclusive or exclusive. If there was a bug that another developer had to fix I'd want to keep the intent as clear as possible.
Upcoming events: * Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
-
sonia_basangar wrote:
Please tell me sql queery which returns the recordset between two date inclusive of two dates
You have not provided enough information to do that. However, I can tell you that in your WHERE clause you will need something that looks like this:
MyDateColumn >= @SomeStartDate AND MyDateColumn <= @SomeEndDate
Remember that for the end date, you may have to set the time element to 23:59 if you are storing a date and time and want the results for any time on the end date.
Upcoming events: * Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
Hi Thanks for reply MyDateColumn >= @SomeStartDate AND MyDateColumn <= @SomeEndDate this is perfect this is waht i did but when @SomeStartDate and @SomeEndDate are same then it dont return the records for same date not works.
-
I always avoid BETWEEN because it is not clear whether it is inclusive or exclusive. If there was a bug that another developer had to fix I'd want to keep the intent as clear as possible.
Upcoming events: * Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
-
sonia_basangar wrote:
Please tell me sql queery which returns the recordset between two date inclusive of two dates
You have not provided enough information to do that. However, I can tell you that in your WHERE clause you will need something that looks like this:
MyDateColumn >= @SomeStartDate AND MyDateColumn <= @SomeEndDate
Remember that for the end date, you may have to set the time element to 23:59 if you are storing a date and time and want the results for any time on the end date.
Upcoming events: * Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
Hi Colin Thanks for your mail but still my problem is not get resolved ........... MyDateColumn >= @SomeStartDate AND MyDateColumn <= @SomeEndDate this is perfect this is what i did but when @SomeStartDate and @SomeEndDate are same then it dont return the records .
-
Hi Thanks for reply MyDateColumn >= @SomeStartDate AND MyDateColumn <= @SomeEndDate this is perfect this is waht i did but when @SomeStartDate and @SomeEndDate are same then it dont return the records for same date not works.
If the start and end date are the same and nothing is returned then there is no data for that date. Keep in mind what I said about time shifting the end date to 23:59 in case the dates stored in the database contain time information also. e.g. You want to find stuff on 25/Apr/2007. If you create a datetime with only the date part, the time part will be 00:00 (midnight). If you have data marked as 25/Apr/2007 09:36 then the query will not return anything because the data is not at midnight. This is why you have to set your end date to 25/Apr/2007 23:59 to ensure that you get everything
Upcoming events: * Glasgow: Introduction to AJAX (2nd May), SQL Server 2005 - XML and XML Query Plans, Mock Objects, SQL Server Reporting Services... Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton My website
-
Hi Colin Thanks for your mail but still my problem is not get resolved ........... MyDateColumn >= @SomeStartDate AND MyDateColumn <= @SomeEndDate this is perfect this is what i did but when @SomeStartDate and @SomeEndDate are same then it dont return the records .
What Mr.Colin is said right. He says that some of Ur date Column will have date like 03:03:2007:20:20:20 i.e with hrs and minutes. Try This: floor(Cast(MyDateColumn as float)) >= floor(cast(@SomeStartDate as float)) AND floor(cast(MyDateColumn as float)) <= floor(cast( @SomeEndDate as float))
Regards, Arun Kumar.A
-
Hi Colin Thanks for your mail but still my problem is not get resolved ........... MyDateColumn >= @SomeStartDate AND MyDateColumn <= @SomeEndDate this is perfect this is what i did but when @SomeStartDate and @SomeEndDate are same then it dont return the records .
try like this MyDateColumn >= "01-Apr-2007 12:00:00 AM" AND MyDateColumn <= "01-Apr-2007 11:59:59 PM" Make sure urself - the value i gave in double quotes for just sample. So u have to convert this into date in the SQL
Regards R.Arockiapathinathan