Using 'LIKE' in database explorer to select variables corresponding to substring.

Right so I have a SQL database that I'm analysing using the database toolbox and I want to select all variables corresponding to a particular date. Problem is that the dates currently include the time that the variable was measured, e.g. '2014-01-01 00:00:00'. I looked online and thought the following should work...

WHERE data.date LIKE '2014-01-01%'

to select variables where data.date starts with '2014-01-01'. When I implement this I don't get an error but nothing seems to be happening. Any ideas? Thanks in advance.

Answer 1

[Piyush Kumar]

Hi,

The query you have shared should work as expected, selecting all records where the date column starts with ‘2014-01-01’. If you’re not seeing any results, here are a few things to check:

1. Ensure the date format matches: Verify that the date format in your database matches the format you’re using in the LIKE clause.
2. Check for leading/trailing spaces: Ensure there are no leading or trailing spaces in the date strings in your database.
3. Verify the column name: Make sure the column name date is correct and matches the column name in your database.

If you still encounter issues, you might want to try using the CONVERT function to ensure the date format is consistent:

SELECT * FROM data WHERE CONVERT(datetime, date, 120) LIKE '2014-01-01%';

This converts the date column to a string in the format ‘yyyy-mm-dd hh:mi:ss’ before applying the LIKE operator.