SQL Server datetime LIKE select

Question

in MySQL   select   from record where register date like  2009-10-10     What is the syntax in SQL Server

User · Answer

If you do that  you are forcing it to do a string conversion  It would be better to build a start end date range  and use   declare  start datetime   end datetime select  start    2009-10-10    end    2009-11-10  select   from record where register date  gt    start            and register date  lt   end   This will allow it to use the index  if there is one on register date   rather than a table scan

User · Answer

You can also use convert to make the date searchable using LIKE  For example     select convert VARCHAR 40  create date 121      from sys objects where     convert VARCHAR 40  create date 121  LIKE   17 34

User · Answer

There is a very flaky coverage of the LIKE operator for dates in SQL Server   It only works using American date format   As an example you could try       WHERE register date LIKE  oct 10 2009     I ve tested this in SQL Server 2005 and it works  but you ll really need to try different combinations   Odd things I have noticed are    You only seem to get all or nothing for different sub fields within the date  for instance  if you search for  apr 2   you only get anything in the 20th s - it omits 2nd s  Using a single underscore     to represent a single  wildcard  character does not wholly work  for instance  WHERE mydate LIKE  oct   2010   will not return all dates before the 10th - it returns nothing at all  in fact  The format is rigid American   mmm dd yyyy hh mm    I have found it difficult to nail down a process for LIKEing seconds  so if anyone wants to take this a bit further  be my guest   Hope this helps

User · Answer

The LIKE operator does not work with date parts like month or date but the DATEPART operator does   Command to find out all accounts whose Open Date was on the 1st   SELECT      FROM Account   WHERE DATEPART DAY  CAST OpenDt AS DATE     1     CASTING OpenDt because it s value is in DATETIME and not just DATE

User · Answer

You could use the DATEPART   function  SELECT   FROM record  WHERE   DATEPART yy  register date    2009 AND    DATEPART mm  register date    10 AND    DATEPART dd  register date    10    I find this way easy to read  as it ignores the time component  and you don t have to use the next day s date to restrict your selection  You can go to greater or lesser granularity by adding extra clauses  using the appropriate DatePart code  e g   AND    DATEPART hh  register date    12    to get records made between 12 and 1   Consult the MSDN DATEPART docs for the full list of valid arguments

User · Answer

I am a little late to this thread but in fact there is direct support for the like operator in MS SQL server   As documented in LIKE help if the datatype is not a string it is attempted to convert it to a string  And as documented in cast convert documentation       default datetime conversion to string is type 0   100  which is mon dd   yyyy hh miAM  or PM     If you have a date like this in the DB   2015-06-01 11 52 59 057   and you do queries like this   select   from wws invoice where invdate like  Jun   select   from wws invoice where invdate like  Jun 1   select   from wws invoice where invdate like  Jun 1    select   from wws invoice where invdate like  Jun 1 2015    select   from wws invoice where invdate like  Jun   2015       select   from wws invoice where invdate like  Jun 1 2015 11 52AM    you get that row   However  this date format suggests that it is a DateTime2  then documentation says       21 or 121 -- ODBC canonical  with milliseconds  default for time    date  datetime2  and datetimeoffset  -- yyyy-mm-dd hh mi ss mmm 24h    That makes it easier and you can use   select   from wws invoice where invdate like  2015-06-01     and get the invoice record  Here is a demo code   DECLARE  myDates TABLE  myDate DATETIME2   INSERT INTO  myDates  myDate  VALUES   2015-06-01 11 52 59 057      2015-06-01 11 52 59 054      2015-06-01 13 52 59 057      2015-06-01 14 52 59 057     SELECT   FROM  myDates WHERE myDate LIKE  2015-06-01    SELECT   FROM  myDates WHERE myDate LIKE  2015-06-01 11    SELECT   FROM  myDates WHERE myDate LIKE  2015-06-01 11 52 59    SELECT   FROM  myDates WHERE myDate LIKE  2015-06-01 11 52 59 054      Doing datetime searches in SQL server without any conversion to string has always been problematic  Getting each date part is an overkill  which unlikely would use an index   Probably a better way when you don t use string conversion would be to use range checks  ie   select   from record  where register date  gt    20091010  and register date  lt   20091011

User · Answer

Unfortunately  It is not possible to compare datetime towards varchar using  LIKE  But the desired output is possible in another way       select   from record where datediff dd  record   register date   2009-10-10   0

User · Answer

You can use CONVERT to get the date in text form   If you convert it to a varchar 10   you can use   instead of like   select   from record where CONVERT VARCHAR 10  register date 120     2009-10-10    Or you can use an upper and lower boundary date  with the added advantage that it could make use of an index   select   from record where  2009-10-10   lt   register date and register date  lt   2009-10-11

User · Answer

Try this  SELECT top 10   from record WHERE  IsActive   1 and CONVERT VARCHAR  register date  120  LIKE  2020-01

User · Answer

I solved my problem that way  Thank you for suggestions for improvements  Example in C    string dd  mm  aa  trc  data  dd   nData Text Substring 0  2   mm   nData Text Substring 3  2   aa   nData Text Substring 6  4   trc    -   data   aa   trc   mm   trc   dd    Select   From bdPedidos Where Data Like       data

User · Answer

There s no direct support for LIKE operator against DATETIME variables  but you can always cast the DATETIME to a VARCHAR   SELECT  list of fields  FROM YourTable WHERE CONVERT VARCHAR 25   register date  126  LIKE  2009-10-10     Check the MSDN docs for a complete list of available  styles  in the CONVERT function   Marc

[sql-server] SQL Server datetime LIKE select?

Examples related to sql-server

Examples related to sql-like