phpmyadmin Question posted in
The official documentation can be found here.

LIKE operator for DATE type variable in T-SQL – Phpmyadmin

I want to run a condition like this:

SELECT COUNT(*) FROM table WHERE date_time LIKE '2019-02-02';

I’ve tried converting it into T-SQL which is something like this:

SELECT COUNT(*) FROM table WHERE date_time LIKE [dynamicDateVariable];

which results to how many rows are in that date right?

Now I tried putting it in an IF statement, like this:

IF (SELECT COUNT(*) FROM funnel_logs 
    WHERE date_time LIKE converted_date) = '1' THEN
    //... run code
ELSE
    //... run this code
END IF;

My Goal is to only get one(1) row count to run code but the problem is even though I have a one(1) row count for that date it still proceed to the ELSE statement and runs the run this code.

Note: I’m using xampp and phpmyadmin to run this, and I’m running this process on a trigger. I’ve tried using the CONVERT(VARCHAR, [dynamicDateVariable], 126) LIKE [dynamicDateVariable] but it still doesn’t work. There was also a DATEPART() but some says that it’s not really considerable and I think having a [dynamicDateVariable] would have too much work to be processed.

Am I missing something? I’m new to T-SQL, so if anyone can help it would be much appreciated.

Tags:

4

Answers


  1. 0

    The reason is that you cannot perform LIKE operator with DateTime this way and for that you’ll have to think of a work around.

    • Split your converted_date date in Years, months and days (date you
      want to perform LIKE with)
    • Use DatePart function to get separate Year, Month and Day Then
      perform Comparison.

    Following is the code I tested myself on SqlServer:

    declare @var int 
set @var = (SELECT 
                  COUNT(*) FROM t  
            WHERE --ddate LIKE '2019-02-22%')
                 (DATEPART(yy, ddate) = 2019) AND
                  DATEPART(mm, ddate) = 02 AND
                  DATEPART(dd, ddate) = 22)
IF (@var = 1) 
begin
    select 1
end
ELSE
begin
    select 2
END
    Login or Signup to reply.
  2. 0

    In T-SQL, the LIKE operator only works with string data types.

    From official documentation:

    Determines whether a specific character string matches a specified pattern.

    And later on:

    Arguments
    match_expression
    Is any valid expression of character data type.

    Best practice for searching records where a datetime/datetime2 column has a specific date (without time) is using >= for the date requested, and <= for one day after:

    DECLARE @SearchDate Date = '2019-02-22';

SELECT Columns
FROM Table
WHERE DateTimeColumn >= @SearchDate
AND DateTimeColumn <= DATEADD(DAY, 1, @SearchDate)

    You can search for different ranges the same way – for instance – if you want to search for a particular month, set the date to the first of that month and instead of adding a day, add a month:

    DECLARE @SearchDate Date = '2019-02-01';

SELECT Columns
FROM Table
WHERE DateTimeColumn >= @SearchDate
AND DateTimeColumn <= DATEADD(MONTH, 1, @SearchDate)
    Login or Signup to reply.
  3. 0

    Your SQL script is not working because you are comparing integer with string.
    i.e. 1 = ‘1’
    Change ‘1’ to 1 as below then it should work:

    IF (SELECT COUNT(*) FROM funnel_logs
    WHERE date_time LIKE converted_date) = 1 THEN
    //… run code
    ELSE
    //… run this code
    END IF;

    Login or Signup to reply.
  4. 0

    You can use LIKE on character datatypes but not datetime. Datetime works differently. Have you tried using variables and then using >= and < operator like below?

    DECLARE @Date1 date=‘2019-02-02;
DECLARE @Date2 date=DATEADD(day,1, @date1); 

SELECT COUNT(*) 
FROM table 
WHERE date_time >= @Date1 
  AND date_time < @Date2;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search