Avatar of Michael Ambech
Michael Ambech

asked on 

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
ASPMySQL Server

Avatar of undefined
Last Comment
Big Monty
Avatar of Big Monty
Big Monty
Flag of United States of America image

can you post the code you use to save the data?
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?
Avatar of Michael Ambech
Michael Ambech

ASKER

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.
Avatar of Big Monty
Big Monty
Flag of United States of America image

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

Avatar of Michael Ambech

ASKER

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
Avatar of Michael Ambech

ASKER

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!
ASKER CERTIFIED SOLUTION
Avatar of Big Monty
Big Monty
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Michael Ambech

ASKER

I am not worthy - awesome! Thanks! That did the trick!
Avatar of Michael Ambech

ASKER

As always brilliant - patient and persistent!
Avatar of Big Monty
Big Monty
Flag of United States of America image

glad to have been able to help :)
ASP
ASP

Active Server Pages (ASP) is Microsoft’s first server-side engine for dynamic web pages. ASP’s support of the Component Object Model (COM) enables it to access and use compiled libraries such as DLLs. It has been superseded by ASP.NET, but will be supported by Internet Information Services (IIS) through at least 2022.

82K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo