CODEWITHSUNDEEP
phpmysqlsql
methuselah
methuselah

Reputation: 13206

Date clashes between form and database

If the user provides a date in the format via aform ($date_booked):

2012-1-6

to be compared against a date (date_booked):

2012-01-06

What would be the correct SQL method to check it be (using phpMyAdmin):

SELECT * FROM room WHERE date_booked LIKE $date_booked

Or would MySQL not return anything?

Thanks in advance.

Upvotes: 2

Views: 111

Answers (2)

Kaf
Kaf

Reputation: 33819

Assuming you are using TSQL and user entered a correct date

SELECT * FROM room WHERE date_booked = CONVERT(date, @UserEnteredDate)

As you have updated your question to MySQL

Here is a good example on how to compare date MySQL - Change date string to date type in place?

Upvotes: 1

Lix
Lix

Reputation: 47976

Assuming PHP from the $variable notation

Using the date_parse() function in PHP it is easy to compare the two dates even though they appear syntactically different :

date_parse('2012-1-6');

array (
  'year' => 2012,
  'month' => 1,
  'day' => 6,
  'hour' => false,
  'minute' => false,
  'second' => false,
  ...
)



date_parse('2012-01-06');

array (
  'year' => 2012,
  'month' => 1,
  'day' => 6,
  'hour' => false,
  'minute' => false,
  'second' => false,
  ...
)

On a side note you should consider storing your date/time information in an Unix timestamp (seconds since 01/01/1970) in your database - much easier to perform calculations on. PHP has a weath of date/time functions.

If you do end up storing your dates and times as Unix timestamp then you could use PHP's strtotime() function :

strtotime — Parse about any English textual datetime description into a Unix timestamp

Upvotes: 2

Related Questions