Date Format

[jeeves245]

Hi guys,

 

i'm currently building a website for a friend. It's 90% finished, but i'm having a few issues which I can't figure out.

 

Firstly, formatting a date in MySQL.. I've tried several different uses of DATE_FORMAT but I can't get it right. Could anyone give me any pointers? The date needs to be inserted as DD-MM-YYYY AND outputted as DD-MM-YYYY.

 

The insert code:

$sql="INSERT INTO TABLE_DELIVERIES (PSNUMBER, DELIVERYDATE)
VALUES
('$_POST[psnumber]','$_POST[deliverydate]')";

 

The output code:

("SELECT PSNUMBER,DELIVERYDATE FROM TABLE_DELIVERIES");

 

I have tried

date_format(DELIVERYDATE,'%d/%m/%Y')

(found somewhere on Google) but it didn't work (date doesn't output at all).

 

Any info/tips would be greatly appreciated

Cheers

 

[fenway]

MySQL date format is YYYY-MM-DD... store it this way, and use DATE_FORMAT() to make it pretty on the way out.

[jeeves245]

MySQL date format is YYYY-MM-DD... store it this way, and use DATE_FORMAT() to make it pretty on the way out.

 

Yep that's what i'm trying to do.. but no luck so far..

[The Eagle]

DATE()

 

DATE_FORMAT()

 

THEDATE()

[jeeves245]

DATE()

 

DATE_FORMAT()

 

THEDATE()

 

Thanks for your reply, but that doesn't mean much to me. I know a decent amount about PHP but i'm still a bit of a beginner...

 

I was hoping someone would be able to tell me how to write the line of code (or at least point me in the right direction)

[PFMaBiSmAd]

To format your incoming DD-MM-YYYY as a DATE data type (YYYY-MM-DD), you can explode it on the '-' character, which will also allow you to validate it (all external data cannot be trusted and must be validated before you attempt to use it and in this case you must also use mysql_real_escape_string on the resulting value put into the query to prevent sql injection) using checkdate. You can then produce the YYYY-MM-DD format from the exploded parts.

 

To select a DATE value (YYYY-MM-DD) and format it the way you want, use the mysql DATE_FORMAT() function in your select query. I would recommend also using an alias name for the value to make referencing it in the php code easier. The '%d/%m/%Y' format string you came up with does not match the stated format of DD-MM-YYYY. It would need to be '%d-%m-%Y'

[PFMaBiSmAd]

Here is further information on your $_POST[deliverydate] DD-MM-YYYY values. Expecting a human to consistently and correctly proved you a value in that format is not going to happen. You are going to get all possible variations - DD-MM-YY, DD/MM/YYYY, DD/MM/YY, DD MM YYYY, DD MM YY, MM-DD-YYYY, MM-DD-YY, MM/DD/YYYY, MM/DD/YY, MM DD YYYY, MM DD YY, and a whole bunch of other possibilities (even if you have a label and an example showing what you want.)

 

The best method is to use clearly labeled, separate, drop-down-selects for each part that makes up the date. Even if someone does not read the labels, seeing a drop-down with 1-12 is a pretty big hint that it means months, and a separate drop down with 1-31 is a pretty big hint that it means days.

[fenway]

Plus most date widgets do this anyway.

[jeeves245]

Thanks for the help. I'll keep working on it and let you know how I go

[jeeves245]

Done! Thanks guys.

 

I ended up changing the database a bit, and instead of having the one date field, I now have DAY, MONTH, YEAR, and then drop down boxes on the input page so no one can mess it up.

 

I have another question now though...

 

I use this code to return all data from the database:

$result = mysql_query("SELECT PSNUMBER,DAY,MONTH,YEAR FROM TABLE_DELIVERIES");

 

The data is listed down the page from the page from the oldest added field first. Is there any way to list it from the most recently added field first?

 

Cheers.

[PFMaBiSmAd]

I now have DAY, MONTH, YEAR

That means that every query that tries to find a date will be more complicated and slower than if it was a DATE data type.

 

Is there any way to list it from the most recently added field first

Yes, use a DATE data type and just use -

 

ORDER BY your_date_field DESC

 

The different data types exist for a reason, use them. If you have a date, store it in a DATE data type.

[jeeves245]

I now have DAY, MONTH, YEAR

That means that every query that tries to find a date will be more complicated and slower than if it was a DATE data type.

 

 

Yes I realise this, but it's a very simple database that will never have anymore than about 10 fields... so i'm just taking the easy way out.

 

Cheers for the info.

[haku]

Why not take the time to learn to do it properly? The point isn't even that it's split into three sections, it's that you lose all the benefits of having a date column in your database.

[fenway]

Yes I realise this, but it's a very simple database that will never have anymore than about 10 fields... so i'm just taking the easy way out.

Oh, it's definitely not going to easier to "fake" a DATE.

[jeeves245]

Ok i've unsolved this topic because I have some more questions..

 

I've been thinking about it,and I guess using 3 separate fields for the date is probably a bad idea.

 

But i'm still extremely confused on how to use the date_format function. I need the date entered by the user to be in DD-MM-YYYY format, and then I need it outputted in the same format when I go to select it from the database.

 

Can anyone help me out with this? I'm guessing it's not too hard......

 

Also, is it possible to make it so the user adds DD-MM to the text box, and PHP adds the current year itself?

 

Any more info is appreciated

[jeeves245]

Anyone?

[PFMaBiSmAd]

Reply #5 in this thread already gave methods of breaking apart the incoming format and producing a DATE format from the exploded parts and gave the correct format string to use in the mysql DATE_FORMAT() function to get a DATE type back into the desired format.

[jeeves245]

Reply #5 in this thread already gave methods of breaking apart the incoming format and producing a DATE format from the exploded parts and gave the correct format string to use in the mysql DATE_FORMAT() function to get a DATE type back into the desired format.

 

Yes but this means nothing to me. Do you know of any tutorials or something that will explain it in more depth?

 

As I said, i'm a beginner...

[fenway]

I need the date entered by the user to be in DD-MM-YYYY format

Doesn't matter at all... you can still put the pieces back together so that you use a date field properly.