Hi, I need to query all values between specified date range and time range. For example, I want to query all the values from 5:00Pm on 16-Sep-2010 to 9:00AM 21-Sep-2010. Any ideas how the query should be.
Thanx in advance.
+2
A:
SELECT * FROM TABLE
WHERE DATE BETWEEN '09/16/2010 05:00:00' and '09/21/2010 09:00:00'
bahadir arslan
2010-09-21 13:51:14
If there cultural problems (may be your application runs different cultures) this syntax may be cause some problems. If you have problems, you can format date like this: 09/16/2010 05:00:00 => 20100916050000'
bahadir arslan
2010-09-21 13:56:21
@bahadir arslan, I would prefer to use '09/21/2010 09:59:59' to get more accurate results.
GS_Guy
2010-09-21 13:57:07
@gs_guy, you are right but we are not sure exact requirement, until 09 am or until 10 am?
bahadir arslan
2010-09-21 13:58:41
Also include the AM/PM designation or specify military time.
adrift
2010-09-21 14:06:47
@Adrift, 20100916050000 format is 24 hour format, so no need to add AM/PM
bahadir arslan
2010-09-21 14:32:22
@bahadir arslan, you would be correct if he wanted values from 5:00 AM, but he wants them from 5:00 PM.
adrift
2010-09-21 19:36:08
@Adrift you are right, i am sorry ;) i think 20100916170000 will be ok for this situation.
bahadir arslan
2010-09-22 06:31:44
I am sorry, I should have explained my question in detail. Anywayz guyz, I've got three coulmns in my table, "Value", "ReadTime", "ReadDate". Where ReadTime and ReadDate having data types of time and date respectively. Now keeping this in mind that I've separate columns for date and time, how would I achieved the results as asked in the main question.
Zubair
2010-09-22 08:42:55
The only safe formats for datetime literals in SQL Server (or at least historically) are: "YYYYMMDD", "YYYY-MM-DD'T'hh:mm:ss" and "YYYY-MM-D'T'hh:mm:ss:mil" (where 'T' is the literal letter T). All other formats are subject to culture issues.
Damien_The_Unbeliever
2010-09-22 08:57:07
A:
How about this?
SELECT Value, ReadTime, ReadDate
FROM YOURTABLE
WHERE CAST(ReadDate AS DATETIME) + ReadTime BETWEEN '2010-09-16 17:00:00' AND '2010-09-21 09:00:00'
EDIT: output according to OP's wishes ;-)
eumiro
2010-09-22 08:53:14
Thnx but I need the result set in the same format i.e "Value","ReadTime","ReadDate" ( I don't want to merge date and time )
Zubair
2010-09-22 09:01:38
A:
Infact this worked for me
SELECT *
FROM myTable
WHERE CAST(ReadDate AS DATETIME) + ReadTime BETWEEN '2010-09-16 5:00PM' AND '2010-09-21 9:00AM'
Zubair
2010-09-22 09:44:32