sql query problem

Question

i want to compare two date and time with each other and i want to select records that "StartTime" is greater than now time and the "StartDate" is greater than now date.

but output is not correct.thanks in advance. my tabale'records are:

StartDate       StartTime
-------------------------------
1389/07/11  11:04
1389/06/23  21:17
1389/06/23  21:32
1389/06/23  22:10
1389/06/26  12:34
1389/06/27  17:29
1389/06/27  18:13
1389/06/27  20:27
1389/06/28  09:41
1389/07/18  10:46
1389/07/05  22:00
1389/07/15  24:00

output is: when the query is : (1)

SELECT        StartDate, StartTime
FROM            Proj
WHERE        (StartDate < '1389/07/15 ') AND (StartTime <= '20:20 ')

StartDate       StartTime   
-------------------------------      
1389/07/11  11:04
1389/06/26  12:34
1389/06/28  09:41
1389/07/18  10:46

output is: when the query is: (2)

SELECT        StartDate, StartTime
FROM            Proj
WHERE        (StartDate > '1389/07/15 ') AND (StartTime >= '20:20 ')

StartDate       StartTime   
------------------------------- 
NULL    NULL

the correct output should be:

StartDate       StartTime   
------------------------------- 
1389/07/18  10:46

i use persian date.

Answer 1

Just taking what you have above and the description of your problem, the query should be:

select * from test where date>'2010/10/05' and time>='20:22'

If you post more details about your problem and the schema in which you're working we'll be able to help you more.

Answer 2

I want to select records that "time" is greater than now time and the "date" is greater than now date.

First off to get the current datetime (your now), you can use the SQL function GETDATE(). So if you happen to have a datetime column you could just do

SELECT * FROM Test WHERE LogDateTime >= GETDATE()

This will return every record in the table Test of which the datetime value inside the LogDateTime column is in the future.

Now, although it's a little bit more complicated, the same can be used when you have split the date and the time into separate columns.

SELECT  * FROM Test 
WHERE   CONVERT(datetime, LogDate + ' ' + LogTime) >= GETDATE()

If LogDate or LogTime are nullable columns you could use ISNULL(<columnName>, <defaultvalue>) to be safe.

Answer 3

it solved:

SELECT        StartDate, StartTime
FROM            Proj
WHERE        (StartTime < '20:20 ') AND (StartDate = '1389/07/15') OR
                         (StartDate > '1389/07/15')