Problem with date format mysql - classic asp

Michael Ambech
Michael Ambech used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Big MontyWeb Ninja at large

Commented:
can you post the code you use to save the data?
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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?

Author

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.
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Big MontyWeb Ninja at large

Commented:
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

Author

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

Author

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!
Web Ninja at large
Commented:
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

Author

Commented:
I am not worthy - awesome! Thanks! That did the trick!

Author

Commented:
As always brilliant - patient and persistent!
Big MontyWeb Ninja at large

Commented:
glad to have been able to help :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial