How can I compare time in SQL Server

Question

I m trying to compare time in a datetime field in a SQL query  but I don t know if it s right  I don t want to compare the date part  just the time part   I m doing this   SELECT timeEvent  FROM tbEvents  WHERE convert datetime  startHour  8   gt   convert datetime   startHour  8    Is it correct    I m asking this because I need to know if 08 00 00 is less or greater than 07 30 00 and I don t want to compare the date  just the time part   Thanks

User · Answer

below query gives you time of the date   select DateAdd day -DateDiff day 0 YourDateTime  YourDateTime  As NewTime from Table

User · Answer

SELECT timeEvent    FROM tbEvents   WHERE CONVERT VARCHAR startHour 108   gt    01 01 01    This tells SQL Server to convert the current date time into a varchar using style 108  which is  hh mm ss    You can also replace  01 01 01  which another convert if necessary

User · Answer

If you re using SQL Server 2008  you can do this   WHERE CONVERT time 0   startHour   gt   CONVERT time 0    startTime    Here s a full test   DECLARE  tbEvents TABLE       timeEvent   int      IDENTITY      startHour   datetime    INSERT INTO  tbEvents  startHour  SELECT DATEADD hh  0  GETDATE    INSERT INTO  tbEvents  startHour  SELECT DATEADD hh  1  GETDATE    INSERT INTO  tbEvents  startHour  SELECT DATEADD hh  2  GETDATE    INSERT INTO  tbEvents  startHour  SELECT DATEADD hh  3  GETDATE    INSERT INTO  tbEvents  startHour  SELECT DATEADD hh  4  GETDATE    INSERT INTO  tbEvents  startHour  SELECT DATEADD hh  5  GETDATE     --SELECT   FROM  tbEvents  DECLARE  startTime  datetime  SET  startTime   DATEADD mi  65  GETDATE     SELECT     timeEvent      CONVERT time 0   startHour   AS  startHour       CONVERT time 0    startTime  AS   startTime  FROM  tbEvents WHERE CONVERT time 0   startHour   gt   CONVERT time 0    startTime

User · Answer

if  cast  2012-06-20 23 49 14 363  as time  between      cast  2012-06-20 23 49 14 363  as time  and      cast  2012-06-20 23 49 14 363  as time

User · Answer

Use Datepart function   DATEPART datepart  date    E g      SELECT DatePart  YourVar  hh  60      DatePart  YourVar  mi  60    This will give you total time of day in minutes allowing you to compare more easily   You can use DateDiff if your dates are going to be the same  otherwise you ll need to strip out the date as above

User · Answer

ronmurp raises a valid concern - the cast floor approach returns different values for the same time  Along the lines of the answer by  littlechris and for a more general solution that solves for times that have a minute  seconds  milliseconds component  you could use this function to count the number of milliseconds from the start of the day   Create Function  dbo   MsFromStartOfDay     DateTime datetime   Returns int As   Begin       Return                    Datepart  ms    DateTime                        Datepart  ss    DateTime     1000                      Datepart  mi    DateTime     1000   60                      Datepart  hh    DateTime     1000   60   60                     End   I ve verified that it returns the same int for two different dates with the same time  declare  first datetime set  first    1900-01-01 23 59 39 090  declare  second datetime set  second    2000-11-02 23 56 39 090  Select dbo MsFromStartOfDay   first   Select dbo MsFromStartOfDay   second     This solution doesn t always return the int you would expect   For example  try the below in SQL 2005  it returns an int ending in  557  instead of  556    set  first    1900-01-01 23 59 39 556  set  second    2000-11-02 23 56 39 556    I think this has to do with the nature of DateTime stored as float   You can still compare the two number  though   And when I used this approach on a  real  dataset of DateTime captured in  NET using DateTime Now   and stored in SQL  I found that the calculations were accurate

User · Answer

You can create a two variables of datetime  and set only hour of date that your need to compare   declare   date1 datetime  declare   date2 datetime   select    date1   CONVERT varchar 20  CONVERT datetime   2011-02-11 08 00 00    114  select    date2   CONVERT varchar 20  GETDATE    114    The date will be  1900-01-01  you can compare it  if  date1  lt    date2    print   date1 less then  date2  else    print   date1 more then  date2

User · Answer

One  possibly small  issue I have noted with the solutions so far is that they all seem to require a function call to process the comparison  This means that the query engine will need to do a full table scan to seek the rows you are after - and be unable to use an index  If the table is not going to get particularly large  this probably won t have any adverse affects  and you can happily ignore this answer    If  on the other hand  the table could get quite large  the performance of the query could suffer   I know you stated that you do not wish to compare the date part - but is there an actual date being stored in the datetime column  or are you using it to store only the time  If the latter  you can use a simple comparison operator  and this will reduce both CPU usage  and allow the query engine to use statistics and indexes  if present  to optimise the query   If  however  the datetime column is being used to store both the date and time of the event  this obviously won t work  In this case if you can modify the app and the table structure  separate the date and time into two separate datetime columns  or create a indexed view that selects all the  relevant  columns of the source table  and a further column that contains the time element you wish to search for  use any of the previous answers to compute this  - and alter the app to query the view instead

User · Answer

I believe you want to use DATEPART  hour   datetime    Reference is here   http   msdn microsoft com en-us library ms174420 aspx

User · Answer

Just change convert datetime to time that should do the trick   SELECT timeEvent  FROM tbEvents  WHERE convert time  startHour   gt   convert time   startHour

User · Answer

Using float does not work   DECLARE  t1 datetime   t2 datetime SELECT  t1    19000101 23 55 00    t2    20001102 23 55 00  SELECT CAST  t1 as float  - floor CAST  t1 as float    CAST  t2 as float  - floor CAST  t2 as float     You ll see that the values are not the same  SQL Server 2005   I wanted to use this method to check for times around midnight  the full method has more detail  in which I was comparing the current time for being between 23 55 00 and 00 05 00

User · Answer

convert varchar 5   thedate  108  between  leftTime and  rightTime   Explanation   if you have varchar 5  you will obtain HH mm  if you have varchar 8  you obtain HH mm ss  108 obtains only the time from the SQL date   leftTime and  rightTime are two variables to compare

User · Answer

I am assuming your startHour column and  startHour variable are both DATETIME  In that case  you should be converting to a string   SELECT timeEvent FROM tbEvents WHERE convert VARCHAR 8   startHour  8   gt   convert VARCHAR 8    startHour  8

User · Answer

TL DR Separate the time value from the date value if you want to use indexes in your search  you probably should  for performance   You can   1  use function-based indexes or  2  create a new column for time only  index this column and use it in you SELECT clause   Keep in mind you will lose any index performance boost if you use functions in a SQL s WHERE clause  the engine has to do a scan search  Just run your query with EXPLAIN SELECT    to confirm this  This happens because the engine has to process EVERY value in the field for EACH comparison  and the converted value is not indexed  Most answers say to use float    convert    cast    addtime    etc   Again  your database won t use indexes if you do this  For small tables that may be OK  It is OK to use functions in WHERE params though  where field   func value    because you won t be changing EACH field s value in the table  In case you want to keep use of indexes  you can create a function-based index for the time value  The proper way to do this  and support for it  may depend on your database engine  Another option is adding a column to store only the time value and index this column  but try the former approach first   Edit 06-02 Do some performance tests before updating your database to have a new time column or whatever to make use of indexes  In my tests  I found out the performance boost was minimal  when I could see some improvement  and wouldn t be worth the trouble and overhead of adding a new index

User · Answer

I don t love relying on storage internals  that datetime is a float with whole number   day and fractional   time   but I do the same thing as the answer Jhonny D  Cano  This is the way all of the db devs I know do it  Definitely do not convert to string  If you must avoid processing as float int  then the best option is to pull out hour minute second milliseconds with DatePart

User · Answer

Your compare will work  but it will be slow because the dates are converted to a string for each row   To efficiently compare two time parts  try   declare  first datetime set  first    2009-04-30 19 47 16 123  declare  second datetime set  second    2009-04-10 19 47 16 123   select  cast  first as float  - floor cast  first as float    -         cast  second as float  - floor cast  second as float           as Difference   Long explanation  a date in SQL server is stored as a floating point number  The digits before the decimal point represent the date  The digits after the decimal point represent the time   So here s an example date   declare  mydate datetime set  mydate    2009-04-30 19 47 16 123    Let s convert it to a float   declare  myfloat float set  myfloat   cast  mydate as float  select  myfloat -- Shows 39931 8244921682   Now take the part after the digit  i e  the time   set  myfloat    myfloat - floor  myfloat   select  myfloat -- Shows 0 824492168212601   Convert it back to a datetime   declare  mytime datetime set  mytime   convert datetime  myfloat  select  mytime -- Shows 1900-01-01 19 47 16 123   The 1900-01-01 is just the  zero  date  you can display the time part with convert  specifying for example format 108  which is just the time   select convert varchar 32   mytime 108  -- Shows 19 47 16   Conversions between datetime and float are pretty fast  because they re basically stored in the same way

User · Answer

Adding to the other answers   you can create a function for trimming the date from a datetime  CREATE FUNCTION dbo f trimdate   dat datetime  RETURNS DATETIME AS BEGIN     RETURN CONVERT DATETIME  CONVERT FLOAT   dat  - CONVERT INT   dat   END   So this   DECLARE  dat DATETIME SELECT  dat    20080201 02 25 46 000  SELECT dbo f trimdate  dat    Will return 1900-01-01 02 25 46 000

[sql] How can I compare time in SQL Server?

Examples related to sql

Examples related to sql-server

Examples related to datetime