SQL like clause for dates

I am querying a db to get al开发者_Go百科l blog posts from a certain month of a certain year. The date is stored in the database as YYYY-MM-DD

What is the best foolproof method of getting only the posts of a certain day and year? I have tried chaining like clauses, but I've come to realise a few ways in which they would fail, for example if I used the statement:

'SELECT * FROM entries WHERE date LIKE \''.$year.'%\' AND date LIKE \'%'.$month_no.'%\''

This would fail if the month number happened to be contained within the last 3 digits of the year number. Would I have to use regex and if so, could anyone suggest a statement I could use?

Please.. just use a date filter. It will help performance (use an index)

$query = "SELECT *
FROM entries
WHERE date >= '" . $year . "-" . $month_no . "-01'
  AND date <  adddate('" . $year . "-" . $month_no . "-01', interval 1 month)";

That was for MySQL. For SQL Server, use something similar, but use DATEADD instead

...
WHERE date >= '$year" . $month . "01'
  AND date <  DATEADD(month,1,'$year" . $month . "01'";

The reason for adding one month is that it is easier to handle the case when month is 12 (December).

I would recommend creating another column with datetime (or just date if possible). It would be much more easier going forward.

Alternatively, you can cast it to date type and query it.

I'd have thought the following should solve this:

'SELECT * FROM entries WHERE date LIKE \''.$year.'-'.$month_no.'-%\''

However, you'd need to ensure that $year was four digits long and $month_no was two digits using whatever scripting language, etc. you're creating the query in.

if DATE field is DATE TYPE then 'like' operator don't work, you have to transform date type in varchar type then you can use 'like' operator on result of trasformation.

Bye

Your data is stored as a string in the database not as a date object? Look at creating a date object from the string if possible using to_date or a CAST. There are plenty of date time functions which make this trivial for a date object e.g. DATEPART in T-SQL and PL-SQL or YEAR, MONTH in MySql

• T-SQL Date Time Functions
• PL-SQL Built-in Functions
• MySql Date Time Functions

You don't say which flavour of database you're using. Most products support a DATE datatype and that would be the obvious choice for the column.

But presuming you are actually using a character column then you can easily solve it without REGEX by using a substring() or substr() function:

'SELECT * FROM entries 
WHERE substr(date, 1, 7) =  '.$year.'-'.$month_no

NB Sorry, I don't recognise your SQL dialect, so I may have got the precise syntax wrong.

Foolproof would probably be to convert your strings to dates:

DECLARE @dateToFind datetime

SET @dateToFind = CONVERT(datetime, N'2011-04-02', 20)

SELECT * FROM [table] WHERE CONVERT(datetime, [datefield], 20) = @dateToFind

Or, if you need a range:

DECLARE @startDate datetime, @endDate datetime

SELECT 
    @startDate = CONVERT(datetime, N'2011-04-01', 20), 
    @endDate = CONVERT(datetime, N'2011-04-30', 20) 

SELECT * FROM [table] WHERE CONVERT(datetime, [datefield], 20) BETWEEN @startDate AND @endDate

Or you could convert twice:

SELECT * FROM [table] WHERE YEAR(CONVERT(datetime, [datefield], 20)) = 2011 AND MONTH(CONVERT(datetime, [datefield], 20)) = 4

The number 20 in the CONVERT matches your date format.

I ran into this when I made a database of monitored events (mine was complicated by the fact that in terms of what I was doing, my days actually ran from 8am in the morning, so 01/02/2011 07:59:59" actually would count as 1st Jan!, you can do a number of things

You were after a certain day. You didnt mention which language you're calling the SQL from so..

1. Store the date as dates inc times, and then use convert to convert it to just the date, which you can query as normal (see convert for converstions)

2. Store in dates as above, and use between, so $date1=01/01/2011 00:00:00 and $date2=02/01/2011 00:00:00 would be

   'select * from entries where date between "'.$date1.'" and "'.$date2.'"'
   

3. stick with what you have and do

   'select * from entries where date = "'.$year.'/'.$month.'/'.$day.'"'
   

but at least with the full date and time you could still order them in order of the time.