MySQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 
Go Back   Dev Articles Community Forums > Databases > MySQL Development
Reload this Page

UTC_DATE() query Problem

Discuss UTC_DATE() query Problem in the MySQL Development forum on Dev Articles.
UTC_DATE() query Problem MySQL Development forum to discuss administration, SQL syntax, and other MySQL-related topics. Find help with installing, configuring, and maintaining your MySQL databases.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old July 3rd, 2007, 12:01 PM
CHornJr's Avatar
CHornJr CHornJr is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Nov 2002
Location: New York City
Posts: 236 CHornJr User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 1 h 50 m 36 sec
Reputation Power: 7
Send a message via AIM to CHornJr Send a message via MSN to CHornJr Send a message via Yahoo to CHornJr
Angry UTC_DATE() query Problem
I am receiving the following error message
Quote:
Originally Posted by Error Message from script
1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'classEndTime)+0 AS EndTime, * FROM class WHERE courseID='38'' at line 1

I am sure it is something stupid that I can't see in my query statement but I'm not sure what. Here is the actual Query
Quote:
Originally Posted by Query Statement
SELECT UTC_DATE(classEndTime)+0 AS EndTime, * FROM class WHERE courseID='38'
__________________
CHornJr
"One day I'll know what I am doing"
My Blog
Suanhacky Lodge #49
Rebel Squadrons
Reply With Quote
  #2  
Old July 3rd, 2007, 12:18 PM
Bobidybob's Avatar
Bobidybob Bobidybob is offline
Contributing Abuser
Click here for more information
 
Join Date: Apr 2007
Location: Starkville, MS
Posts: 292 Bobidybob User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 Days 3 h 42 m 8 sec
Reputation Power: 2
Send a message via AIM to Bobidybob
what version of mysql are you using? UTC_DATE() is valid for v4.1.1 or later. that may be the problem. if not, then im stumped :P
__________________
Reply With Quote
  #3  
Old July 3rd, 2007, 12:24 PM
Bobidybob's Avatar
Bobidybob Bobidybob is offline
Contributing Abuser
Click here for more information
 
Join Date: Apr 2007
Location: Starkville, MS
Posts: 292 Bobidybob User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 Days 3 h 42 m 8 sec
Reputation Power: 2
Send a message via AIM to Bobidybob
now that i look, are you using UTC_DATE() correctly? i know it returns the current date, but i didnt know you could pass it arguments.
Reply With Quote
  #4  
Old July 3rd, 2007, 12:35 PM
CHornJr's Avatar
CHornJr CHornJr is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Nov 2002
Location: New York City
Posts: 236 CHornJr User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 1 h 50 m 36 sec
Reputation Power: 7
Send a message via AIM to CHornJr Send a message via MSN to CHornJr Send a message via Yahoo to CHornJr
I am using version 5.0

Maybe it can't pass arguments, but the MySQL manual didn't say it couldn't.

The truth is this was just a test because I really am going to need it in my where clause where I have a date column and I want I it be be true only if it is the current date or earlier.

So if UTC_DATE isn't going to work how would be a good way to compare that be. Currently I have

PHP Code:
 $currentDate=getDate();
//format the date so it becomes YYYY-MM-DD to match the date column format

mysql_query=("select * From table where date_column <= '$currentDate'") 
which doesn't work. that is why I have been playing around with UTC_DATE()
Reply With Quote
  #5  
Old July 3rd, 2007, 12:40 PM
Bobidybob's Avatar
Bobidybob Bobidybob is offline
Contributing Abuser
Click here for more information
 
Join Date: Apr 2007
Location: Starkville, MS
Posts: 292 Bobidybob User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 Days 3 h 42 m 8 sec
Reputation Power: 2
Send a message via AIM to Bobidybob
well, if you can figure out the syntax to format the date so its a number YYYYMMDD without the dashes in it, then it should work, as long as you store the date the same.
Reply With Quote
  #6  
Old July 3rd, 2007, 12:43 PM
CHornJr's Avatar
CHornJr CHornJr is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Nov 2002
Location: New York City
Posts: 236 CHornJr User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 1 h 50 m 36 sec
Reputation Power: 7
Send a message via AIM to CHornJr Send a message via MSN to CHornJr Send a message via Yahoo to CHornJr
Date is currently stored as YYYY-MM-DD in the database (its a Date Column) and unfortunetly changing it to another column type isn't going to be possible at the moment.

I was hoping to be able to use UTC to convert it to YYYYMMDD
Last edited by CHornJr : July 3rd, 2007 at 12:44 PM. Reason: Left something out
Reply With Quote
  #7  
Old July 3rd, 2007, 12:47 PM
Bobidybob's Avatar
Bobidybob Bobidybob is offline
Contributing Abuser
Click here for more information
 
Join Date: Apr 2007
Location: Starkville, MS
Posts: 292 Bobidybob User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 Days 3 h 42 m 8 sec
Reputation Power: 2
Send a message via AIM to Bobidybob
use the explode() function. here's the link to the syntax:

http://us3.php.net/manual/en/function.explode.php

you can grab the date, explode it, then compare as an int.
Reply With Quote
  #8  
Old July 3rd, 2007, 01:01 PM
CHornJr's Avatar
CHornJr CHornJr is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Nov 2002
Location: New York City
Posts: 236 CHornJr User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 1 h 50 m 36 sec
Reputation Power: 7
Send a message via AIM to CHornJr Send a message via MSN to CHornJr Send a message via Yahoo to CHornJr
I'm a little confused on how you want me o use the exploda function because the date column in the database is still going to output its conents as YYYY-MM-DD in the whre clause
Reply With Quote
  #9  
Old July 3rd, 2007, 02:07 PM
CHornJr's Avatar
CHornJr CHornJr is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
  
Join Date: Nov 2002
Location: New York City
Posts: 236 CHornJr User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 1 h 50 m 36 sec
Reputation Power: 7
Send a message via AIM to CHornJr Send a message via MSN to CHornJr Send a message via Yahoo to CHornJr
I go it working, I am an idiot. I should have been using CURDATE Function all along. SO It now appears to be working correctly. THanks for your help
Reply With Quote
Reply

Viewing: Dev Articles Community Forums > Databases > MySQL Development > UTC_DATE() query Problem

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 4 hosted by Hostway. 
Stay green...Green IT