0

I have programmed a system which allows administrator to add bacthes. (Basic details + Date of Start and End date). The date format is YYYY/MM/DD. Type : VARCHAR (50). Shoud I use different type (Need expert advice)

I need to list the upcoming and ongoing batches

This is the query which i think would return the ongoing projects

SELECT * FROM PROGRAMMES WHERE 'startdate' <= '$currentdate' AND 'enddate' > '$currentdate'

and for Upcoming projects

`SELECT * FROM PROGRAMMES WHERE 'startdate' >= '$currentdate`'

But I'm unsure about the results.. Since the value is stored in VARCHAR (Would it fetch my required results).. Should i use a different data type for date.. If so please specify

I couldn't understand much from PHP tutorials as everywhere its different.. Could someone guide me with the most commonly used method.

Thanks

Sharan Mohandas
  • 861
  • 1
  • 9
  • 25
  • 1
    MySQL has a DATE and DATETIME data type that you can use for this kind of stuff – chrki Dec 01 '14 at 09:39
  • 2
    You should always store the date in mysql native date data types `date`, `datetime` or `timestamp` the format is `YYYY-mm-dd` now which format is your input `$currentdate` – Abhik Chakraborty Dec 01 '14 at 09:39
  • I haven't started adding batches to db as of now.. So I am open to any format... From what i've read everyone reccommends using YYYY/MM/DD format.. – Sharan Mohandas Dec 01 '14 at 09:42
  • change the data type to `date` and its in `yyyy-mm-dd` format and make sure you enter values in the same format. – Abhik Chakraborty Dec 01 '14 at 09:43
  • yeah sure would do that... Would the conditional operator work perfect then? – Sharan Mohandas Dec 01 '14 at 09:46
  • 1
    yes it should work perfect then if the input values are in the format `yyyy-mm-dd` and more over you can use the inbulit functions like `now()` `curdate()` etc for the comparison. – Abhik Chakraborty Dec 01 '14 at 09:48
  • 1
    Also, make sure your column names are not quoted. `'startdate'` will always be larger than any date string (e.g. `'2050/12/31'`) because `'s' > '2'`. You want `"startdate >= '$currentdate'"`, without single quotes around `startdate`; and as other suggest, do use `DATE` type. Also, obligatory link: Beware [Bobby Tables](http://bobby-tables.com). – Amadan Dec 01 '14 at 10:21

2 Answers2

2

The method I use is the following:

  • always use DATETIME type for datetime.
  • always store dates in single timezone (UTC as base if project will need to show date in different TZ).

You can compare DATETIME columns using operators >, >=, <, <=, =. It is very easy to use and is definitely better then storing date as VARCHAR.

For example, you could find events which have finished last day like this:

... WHERE date_column < NOW() AND date_column > NOW() - INTERVAL 1 DAY

Also, this might help you Best practice for storing the date in MySQL from PHP

Community
  • 1
  • 1
Bogdan Burym
  • 5,482
  • 2
  • 27
  • 46
0

Try this:

SELECT * FROM PROGRAMMES WHERE startdate <= $currentdate AND enddate > $currentdate
jherran
  • 3,337
  • 8
  • 37
  • 54
Pudich Denis
  • 11