0

I have a TimeStamp field in a MySQL database that I'm trying to pull data from. I'm trying to get it as a string, so I've been using the following query:

select CONVERT(VARCHAR, date_created, 120) from junk;

It throws the error:

ERROR 1064 (42000): 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 'VARCHAR, date_modified, 120) from junk limit 10' at line 1

Can someone please tell me what I'm doing wrong?

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
Dezzie
  • 934
  • 3
  • 18
  • 35
  • 1
    MySql and Sql Server are two completley different products. The error message you are using MySql, _not_ Sql Server. – Joel Coehoorn Apr 09 '14 at 15:37
  • Pretty sure that `CONVERT` is not-ANSI standard and is specific to SQL Server. `CAST` is standard but I don't believe allows formatting codes. – Yuck Apr 09 '14 at 15:37
  • VarChar needs a size e.g. VarChar(10) – Tony Hopkinson Apr 09 '14 at 15:37
  • 2
    @TonyHopkinson Actually in SQL Server `varchar` without sizing gets an implicit size of something like 30. Still, the problem is in mixing T-SQL code with MySQL RDBMS. – Yuck Apr 09 '14 at 15:38
  • Aye saw the convert and style immediately put my sql server head on for some reason... – Tony Hopkinson Apr 09 '14 at 15:39
  • Thanks a lot for clarifying that. Bigger question is, how do I go about converting it into a String as I'm pulling it from the DB? – Dezzie Apr 09 '14 at 15:40
  • Hmmm, normally I wouldn't convert a date to a string when pulling from the db. I'd get the date and then let my app deal with formatting as and when. – Tony Hopkinson Apr 09 '14 at 15:46
  • @TonyHopkinson the issue with that is that I'm pulling into Pentaho which is using JDBC and there are quite a lot of NULL TimeStamps (For some odd reason), and I'm to leave them as it is and not assign a 'fake' value to fix the data. The easiest way to handle it would be to convert it to a string and just leave it as it is, as far as I can see. Suggestions are welcome. – Dezzie Apr 09 '14 at 15:54
  • Well if Pentaho can't deal with nulls, that would be an exeception to my 'Normally I wouldn't do that rule. :( – Tony Hopkinson Apr 09 '14 at 15:57
  • @TonyHopkinson it seems to be a [JDBC thing](http://stackoverflow.com/questions/22696902/jdbc-converting-timestamp-to-null-zerodatetimebehavior-issue/22700917?noredirect=1#22700917). – Dezzie Apr 09 '14 at 16:01

3 Answers3

1

CONVERT() in MySQL is used to convert between different character sets, you need DATE_FORMAT():

SELECT DATE_FORMAT(date_created, '%Y%m%d %H%i%S')
FROM Junk

Update: Originally had CAST() incorrectly using VARCHAR(), but CAST() will also work:

SELECT CAST(date_created AS CHAR(10))
FROM Junk

DATE_FORMAT() options

Hart CO
  • 34,064
  • 6
  • 48
  • 63
  • `mysql> select CAST(date_created AS VARCHAR(30)) from junk; ERROR 1064 (42000): 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 'VARCHAR(30)) from junk' at line 1` Any more suggestions? – Dezzie Apr 09 '14 at 15:41
  • 1
    `CAST()` should work as well - but `DATE_FORMAT()` is indeed preferable. – ypercubeᵀᴹ Apr 09 '14 at 15:56
  • @ypercube Why did `CAST()` give him an error? I'm a SQL Server guy, still plenty to learn about MySQL. – Hart CO Apr 09 '14 at 16:02
  • 1
    Ah, it should be `CAST(date_created AS CHAR(30))` or better: `CAST(date_created AS CHAR(10))` – ypercubeᵀᴹ Apr 09 '14 at 16:06
1

If you wanted to format explicitly to yyyymmdd (style 120 in sql server)

Select DATE_FORMAT(somedate, '%Y%m%d') From SomeTable
Tony Hopkinson
  • 20,172
  • 3
  • 31
  • 39
  • Thanks for the answer! The format isn't what I was looking for, but I changed that around to get what I needed. I used the following code: `select DATE_FORMAT(date_created, '%Y-%m-%d %h:%i:%s') from junk;`. Thanks again! – Dezzie Apr 09 '14 at 15:57
0

In MySql, the CONVERT() function is used to convert existing string types (like varchar) between character sets (like utf-8 or ascii). To format a TimeStamp column, use the FROM_UNIXTIME() function.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • `FROM_UNIXTIME` converts all the data in that column to `NULL`. `select FROM_UNIXTIME(date_created) from junk;` is the query I used. – Dezzie Apr 09 '14 at 15:48