Date format on insert

Using the MySQLi server behaviors and the insert function, I have the date as a date picker in DW.
The small issue I'm scratching my head with is when the date is displayed on php page is DD-MM-YYY (Australian format). When the date inserts into the MYSQL Db is swings it to YYYY-MM-DD. As I'm trying to query using a URL variable, I get no results due to the difference.

I've been looking at trying to modify the $InsertQuery statement, so to switch formats on insert, but having no luck with it.

$InsertQuery->bindColumn("date", "s", "".((isset($_POST["date"]))?$_POST["date"]):"") ."", "WA_DEFAULT");

Any help would be great.
Steve

Update this:

$InsertQuery->bindColumn("date", "s", "".((isset($_POST["date"]))?$_POST["date"]):"") ."", "WA_DEFAULT");

to this:

$InsertQuery->bindColumn("date", "t", "".((isset($_POST["date"]))?$_POST["date"]):"") ."", "WA_DEFAULT");

"s" is for "string" and "t" is for "time".... If you use the correct data type it will automatically convert the date to the correct format to work with mySQL.

Thanks Ray
I should have added that i'm just using a varchar field for the date at the moment and not a Date formatted one.
Tried the above and no joy, i'm now wondering if the format in DW is the issue

I'd switch to a date field in the database and I think that will help since you are dealing with a date.

You guys inspired me with this last post.....I'm going to try and use Server Behaviors to "fork" phpNFLPickem.....Can't wait to get started.....Ray, a coffee is coming ur way!!.....Thx again!

I am doing this with the "t" however it is not inserting the correct data value into the mysql table. I get 1969-12-31 ...

The form variable data has 2023-01-29

$InsertQuery->bindColumn("Event", "t", "".((isset($_POST["Event"]))?$_POST["Event"]:"") ."", "WA_DEFAULT");

any thoughts?

Originally Said By: Ray Borduin

  Update this:

$InsertQuery->bindColumn("date", "s", "".((isset($_POST["date"]))?$_POST["date"]):"") ."", "WA_DEFAULT");

to this:

$InsertQuery->bindColumn("date", "t", "".((isset($_POST["date"]))?$_POST["date"]):"") ."", "WA_DEFAULT");

"s" is for "string" and "t" is for "time".... If you use the correct data type it will automatically convert the date to the correct format to work with mySQL.  

What is the value of $_POST["Event"] ? If that value is bad then the code wouldn't convert the time properly. You may want to use "WA_NULL" instead of "WA_BLANK" since a blank date would default to that 1969 date you have.