472,126 Members | 1,427 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,126 software developers and data experts.


I dont really have a problem, but I want to make my code faster and prettier.
I have some tables with DATETIME in it.
They have the std format %Y-%m-%d %H:%i:%s
I need them to be in %d.%m.%Y %H:%i format.

I tried to fix it with the server variable "datetime_format".
After reading a lot in mysql doc I found out that:
This variable is unused.

So Im using
Expand|Select|Wrap|Line Numbers
  1.     DATE_FORMAT(date,'%d.%m.%Y %H:%i') //on SELECT
  2.     STR_TO_DATE(insert,'%Y-%m-%d %H:%i:%s') //on INSERT  
on every query.

After sleeping a night over it I had an idea... Is it possible to write some INSERT/SELECT/UPDATE/ trigger that changes format on every field that is DATETIME?
Or does someone have another idea?
Dec 19 '09 #1
2 2850
5,058 Expert 4TB

DATETIME fields aren't actually strings in any specific format. The actual date and time information is stored as numbers, and then converted to the "Y-m-d H:i:s" format when you retrieve them.

Which means, no. You can't change the format of a DATETIME field until you fetch the data. Then you can use the DATE_FORMAT function to specify in which format you would like them to be converted before they are returned.

If you wanted to store the date in a particular format, then you would have to store it as a string, which is a horrible idea.

In any case, this sort of manipulation of the data should ideally be done on the front-end, or in the SQL queries, but it should never be done to the data itself. The data should always be in neutral form; not specific to any one front-end.
Dec 20 '09 #2
I dont want to store the datetime in a diffrent format...
I want to edit them on every INSERT UPDATE or SELECT
INSERT 20.01.2010 --> 2010-01-20
SELECT 2010-01-20 --> 20.01.2010
so that I dont have to change it on every query.
Dec 23 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Neal | last post: by
5 posts views Thread by PKin via DotNetMonster.com | last post: by
reply views Thread by ZoombyWoof | last post: by
6 posts views Thread by Brandon | last post: by
4 posts views Thread by Michael Sharman | last post: by
3 posts views Thread by len | last post: by
reply views Thread by Edwin.Madari | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.