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
BrogrimInformation Systems Development ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
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.
0
Kevin CrossChief Technology OfficerCommented:
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.
0
BrogrimInformation Systems Development ManagerAuthor Commented:
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
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

PortletPaulfreelancerCommented:
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
;
0
PortletPaulfreelancerCommented:
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
0
BrogrimInformation Systems Development ManagerAuthor Commented:
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
;
0
BrogrimInformation Systems Development ManagerAuthor Commented:
correction is returning null values instead of the date
0
PortletPaulfreelancerCommented:
I took the 2 rows of data supplied, ran the query after removing the incorrect comma and dates were displayed in required format.

Could you try this perhaps?

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

and provide us some rows that are displaying nulls in columns 1 and 2?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BrogrimInformation Systems Development ManagerAuthor Commented:
I have used the LEFT function, it is working.
0
BrogrimInformation Systems Development ManagerAuthor Commented:
Thanks
0
PortletPaulfreelancerCommented:
No problem! Thank you. Cheers, Paul
0
Kevin CrossChief Technology OfficerCommented:
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.
0
BrogrimInformation Systems Development ManagerAuthor Commented:
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.
0
Kevin CrossChief Technology OfficerCommented:
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
0
PortletPaulfreelancerCommented:
>>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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.