how can i check date, hour and minutes only?

I am using this code to insert starttime and endtime.

INSERT INTO working_schedule (id_number, starttime, endtime, created_user, created_pc, created_version, created_domain, created_os, created_workingset) VALUES(@.id_number, @.starttime, @.endtime, @.created_user, @.created_pc, @.created_version, @.created_domain, @.created_os, @.created_workingset)

and this code to check for duplicate before inserting..

IF EXISTS (SELECT id_number, starttime, endtime FROM working_schedule WHERE id_number = @.id_number AND starttime = @.starttime AND endtime = @.endtime)

but it's checking the seconds as well..

how can i only check date, hour and minutes (without the seconds?

If you know that your @.starttime and @.endtime variables NEVER include seconds or milliseconds you can check like this:

AND starttime >= @.starttime
AND starttime < dateadd (mi, 1, @.starttime)
AND endtime >= @.endtime
AND endtime < dateadd (mi, 1, @.endtime)

If your @.starttime and @.endtime variables might include seconds or milliseconds you can do your comparisons like this:

AND starttime >= convert (datetime, convert(varchar(20), @.starttime, 100))
AND starttime < dateadd (mi, 1, convert (datetime, convert(varchar(20), @.starttime, 100)))
AND endtime >= convert (datetime, convert(varchar(20), @.endtime, 100))
AND endtime < dateadd (mi, 1, convert (datetime, convert(varchar(20), @.endtime, 100)))

|||

Use the datediff function:

SELECT id_number, starttime, endtime FROM working_schedule
WHERE id_number = @.id_number
AND DATEDIFF(mi,starttime,@.starttime) = 0
AND DATEDIFF(mi,endtime,@.endtime) = 0

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de