Problem with date format mysql - classic asp

Hi all,

I'm having an issue with updating a date field in classic asp.
Data is stored in mysql.
Issue: Users are given the option to update the projectdeadline - However if they choose not to, the current value should stay in the database.

When the user gets to the form, the date is (if one has been entered) displayed dd-mm-yyyy. If the user actively chooses a new date the data displayed in the form field changes to the form field required by mysql (yyyy-mm-dd). But if they do nothing and just leave it there, the date is still displayed dd-mm-yyyy and thus does not get accepted as a valid date by the mysql database. Any ideas?

BR
Michael
Michael AmbechAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Big MontyWeb Ninja at largeCommented:
can you post the code you use to save the data?
Julian HansenCommented:
If MySQL is expecting yyyy-mm-dd where is the dd-mm-yyyy version coming from - is it being formatted after retrieval from the DB?
Michael AmbechAuthor Commented:
Hi both,

Julian: That's what puzzles me as well. Only answer I can come up with is, that it's a Danish website and that makes the date format display in that way?

Here's the code where the data comes from:
First my jquery maing the text field appear as a calendar, where you can pick the date (this works in IE 9, which is a requirement - otherwise I would have used the date form element).
<meta charset="utf-8_danish_ci">
  <title>jQuery UI Datepicker - Default functionality</title>
  <link rel="stylesheet" href="//code.jquery.com/ui/1.10.4/themes/smoothness/jquery-ui.css">
  <script src="//code.jquery.com/jquery-1.9.1.js"></script>
  <script src="//code.jquery.com/ui/1.10.4/jquery-ui.js"></script>
  <link rel="stylesheet" href="/resources/demos/style.css">
  <script>
  $(function() {
    $('#datepicker').datepicker({ dateFormat: 'yy-mm-dd' }).val();
  });
  </script>
</head>

Open in new window

And the code that decides whether the pick a date form element gets shown is dependint on a variablebeing present in the data being viewed.
Projektdeadline = Request.Form("Projektdeadline")

If Opgavetype = "Projekt" Then
Vis_Deadline = "<tr><td><b>Projektdeadline</b><br><input type=text name=Projektdeadline id=datepicker value=" & Projektdeadline & "></td></tr>"
Else
Vis_Deadline = ""
End if

Open in new window

And finally the insert command on the recieving asp page:
	   DATABASE.execute("UPDATE "		&_
	  "   Table "				&_
	  " SET "				&_
" Projektdeadline = '" & Projektdeadline & "'," &_
" WHERE "				&_
	  "   Id = '" & Id & "'" )

Open in new window


Hope this makes sense to some of you. Some of it is in Danish - apologies.
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Learn More!
Big MontyWeb Ninja at largeCommented:
you could try something like:

Projektdeadline = Year( Projektdeadline ) & "-" & Month( Projektdeadline )  & " - " & Day( Projektdeadline )
DATABASE.execute("UPDATE "		&_
	  "   Table "				&_
	  " SET "				&_
" Projektdeadline = '" & Projektdeadline & "'," &_
" WHERE "				&_
	  "   Id = '" & Id & "'" )

Open in new window

Michael AmbechAuthor Commented:
Close!
The output of above code is YYYY-M-DD (one digit in month missing) - But even if I choose 10th of november the problem persists - However now it won't accept the date at all which actually means we're on to something.

Mysql field type is DATE
Michael AmbechAuthor Commented:
An update - If I format the date (if there is any in the database) IN the form element BEFORE it gets send to the database - in the way you prescribed - it almost works - except for the YYYY-M-DD format. If we could only get the MM to work, I'm in business it seems!
Big MontyWeb Ninja at largeCommented:
try this:

y = Year( Projektdeadline ) 

m = Month( Projektdeadline )
if CInt( m ) < 10 then m = "0" & m

d = Day( Projektdeadline )
if CInt( d ) < 10 then d = "0" & d

Projektdeadline = y & "-" & m  & " - " & d
DATABASE.execute("UPDATE "		&_
	  "   Table "				&_
	  " SET "				&_
" Projektdeadline = '" & Projektdeadline & "'," &_
" WHERE "				&_
	  "   Id = '" & Id & "'" )

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Michael AmbechAuthor Commented:
I am not worthy - awesome! Thanks! That did the trick!
Michael AmbechAuthor Commented:
As always brilliant - patient and persistent!
Big MontyWeb Ninja at largeCommented:
glad to have been able to help :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.