Link to home
Start Free TrialLog in
Avatar of Brogrim
BrogrimFlag for Ireland

asked on

MySQL Sub query & Date Format

I have a column that has a time stamp value, an example would be

2011/07/06 19:10

The seconds are not added which are causing me a problem when I use the DATE_FORMAT function.

I am trying to write a sub query using the LEFT function that will extract the date and then convert the values into a date using the DATE_FORMAT.

The 2nd Part is working OK
SELECT LEFT(PrintStatusFront, 10)  AS PrintStatusFront FROM tblCards

Open in new window

I cannot get the whole query to work
SELECT  DATE_FORMAT(PrintStatus, '%Y/%m/%d') FROM 
SELECT LEFT(PrintStatusFront, 10)  AS PrintStatus FROM tblCards

Open in new window


Error Code: 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 'SELECT LEFT(PrintStatusFront, 10)  AS PrintStatus FROM tblCards' at line 2


Any Ideas
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

What is the data type of the column? Is it really a DATETIME? Or is it a string CHAR column with a string that represents a DATETIME? Just checking before we go too far as you are using LEFT(), which is a string function.
In either case, the DATE_FORMAT should work; therefore, please advise what the original error you were getting. If possible, please include a sample of your data as well as the actual table structure (creation script) for testing.

Demo: http://sqlfiddle.com/#!8/05435/1
SELECT DATE_FORMAT(PrintStatusFront, '%Y/%m/%d') AS PrintStatus
FROM tblCards
;

Open in new window


You also may want to look at the DATE() function, which will extract the date portion of the DATETIME.
Avatar of Brogrim

ASKER

This is the message I am getting back when I use

 SELECT  DATE_FORMAT(TransferStatus, '%Y/%m/%d') AS TransferStatus,
		DATE_FORMAT(PrintStatusFront, '%Y/%m/%d') AS PrintStatusFront,
FROM    tblcards

Open in new window

Error Code: 1292. Incorrect datetime value: '2011/07/14 16:13 '
CREATE TABLE `tblcards` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
    `PrintStatusFront` varchar(45) CHARACTER SET latin1 DEFAULT NULL,
    `TransferStatus` varchar(45) CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `ID` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=50718 DEFAULT CHARSET=utf8;

Open in new window


50465      2013/09/11 21:00:42      2013/09/12 09:59
50466      2013/09/11 21:00:42      2013/09/12 09:59
the syntax error is a trailing comma

SELECT  DATE_FORMAT(TransferStatus, '%Y/%m/%d') AS TransferStatus,
            DATE_FORMAT(PrintStatusFront, '%Y/%m/%d') AS PrintStatusFront
FROM    tblcards
;

or, as I actually prefer it "comma first":

SELECT
                DATE_FORMAT(TransferStatus, '%Y/%m/%d') AS TransferStatus
            ,  DATE_FORMAT(PrintStatusFront, '%Y/%m/%d') AS PrintStatusFront
FROM    tblcards
;
you can see this more clearly (i.e. the original reformatted): here

SELECT
        DATE_FORMAT(TransferStatus, '%Y/%m/%d') AS TransferStatus
      , DATE_FORMAT(PrintStatusFront, '%Y/%m/%d') AS PrintStatusFront
      ,
FROM tblcards
Avatar of Brogrim

ASKER

The proposed statement is not returning null values

SELECT
                DATE_FORMAT(TransferStatus, '%Y/%m/%d') AS TransferStatus
            ,  DATE_FORMAT(PrintStatusFront, '%Y/%m/%d') AS PrintStatusFront
FROM    tblcards
;
Avatar of Brogrim

ASKER

correction is returning null values instead of the date
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Brogrim

ASKER

I have used the LEFT function, it is working.
Avatar of Brogrim

ASKER

Thanks
No problem! Thank you. Cheers, Paul
Can you explain to future readers the difference between the accepted comment and the one I posted yesterday? It will help them to determine the root cause as I see no difference in the syntax. As I said then, the DATE_FORMAT works and provided a query with no syntax errors that runs as-is evidenced by the example URL. Therefore, I am just curious what the resolution was here as your follow-up comment states "I have used the LEFT function, it is working," which muddies the waters as LEFT is not involved in either Paul or my comment's code.
Avatar of Brogrim

ASKER

Apologies I guess I got that wrong, I have been struggling with that problem for a while, should have gave more thought to the points.

As you can see from my limited skills there will be plenty more points available. Once again I apologise for the misappropriation of points.
Brogrim, the points are not the issue. The confusion solution was. Given after my post of the same solution, you stated the query did not work. I thought it would be confusing to future readers to see an accepted post with the same query followed by a comment in which you say you went back to LEFT() function.

The fact is you should not need LEFT() at all as the DATE_FORMAT function should work. Therefore, it leads me to believe something else is the problem (e.g., there are trailing spaces after the end of the date). If there are trailing spaces, then the result of DATE_FORMAT will be NULL - consistent with "correction is returning null values instead of the date."
DEMO: http://sqlfiddle.com/#!8/cc45b/1

Therefore, the LEFT() would work because it trims the spaces:
SELECT DATE_FORMAT(LEFT(PrintStatusFront, 10), '%Y/%m/%d') AS PrintStatus
FROM tblCards
;

Open in new window


If that is the case, the TRIM() function may suit you better as it would handle leading spaces also.
SELECT DATE_FORMAT(TRIM(PrintStatusFront), '%Y/%m/%d') AS PrintStatus
FROM tblCards
;

Open in new window

REFMAN: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_trim

The better long-term solution would be to store the dates as DATETIME versus VARCHAR(45).

Anyway, I am glad you got it working now...I just want to make sure you and other readers understand why. Otherwise, you will have difficulty later.

Respectfully yours,

Kevin
>>The better long-term solution would be to store the dates as DATETIME versus VARCHAR(45).

Absolutely!

I also agree with mwvisa1 that the choice of accepted answer, ideally, is helpful to other readers (and that the points are not at issue).

Cheers, Paul