visitor (0 QPoints)
  • FR
  • EN
  • NL
  • DE
  • ES
315 experts, 1193 registered users, 1659 questions already answered
European Experts Exchange, the very best site for high-quality IT solutions

New Improved Search!

 


05/10/2011 1h30 : Steve Jobs is dead, the father of Apple ][ is gone, we are all orphaned.

Databases :: MySql :: MySql query help
By: Squibi U.S.A.  Date: 30/01/2003 00:00:00  English  Points: 50 Status: Answered
Quality : Excellent
The format of the date is: 1/1/2003

I need the format to be converted to the format 2003-01-01 before it gets inserted into the MySQL database.

Can someone help?

This is what I have been working with:

sqlfullclass2="update class set class.classstatus='Available' where class.classname='" & rs2(0) & "' and class.classdate=date_format('" & rs2(1) & "', '%Y-%m-%d')"

It is not working so I would appreciate any assistance! I know to_char does the trick with Oracle, but I'm clueless about MySQL.
By: VGR Date: 30/01/2003 09:21:00 English  Type : Comment
usually I do this in the script calling the mySql statements, like PHP... or the one with your rs(0), rs(1) in it ;-)

Are you trying to do this with pure SQL ? So where does the date in "normal" format DD/MM/YYYY come from ? command line ?
By: Squibi Date: 30/01/2003 09:25:00 English  Type : Comment
The date in "normal format" is a result of another sql query. It picks up data from the mysql database and converts it into that normmal format. Now I want to update that same database table but need to convert the date back to the yyyy-mm-dd format.
By: Squibi Date: 30/01/2003 09:26:00 English  Type : Comment
Oh, I'm writing an asp webpage written in vbscript.

Does that help?
By: VGR Date: 30/01/2003 09:27:00 English  Type : Comment
anyway, if ever your input format is fixed (two digits for day and month), you can use ... and class.classdate=concat(mid(&rs2(1)&,6,4),'-',mid(&rs2(1)&,3,2),'-',mid(&rs2(1)&,0,2))
By: VGR Date: 30/01/2003 09:28:00 English  Type : Answer
well, transform the date in ASP/VBS and pass it in the correct format to the SQL query. Use strcopy()/substr(), strlen(), strpos('/',string) etc etc
By: Squibi Date: 31/01/2003 02:23:00 English  Type : Comment
I really do not know how to do that. My skills are very limited in this area. I would appreciate any samples to explain how to do this.

The suggestion to concat kept shooting back syntax errors.

Thanks so much for your patience.
By: Squibi Date: 31/01/2003 02:49:00 English  Type : Comment
I figured it out with your suggestions. Thanks!

Just so you know I did: classdate="TO_DATE('" & rs2(1) & "', 'MM/DD/RR')"

before the sql update and it worked.

Thanks!

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Twitter Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

EContact
browser fav
page generated in 305.957080 milliseconds

Why Google AdSense ads ?
compteur
 Ranking-Hits PageRank for this page