469,625 Members | 1,094 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,625 developers. It's quick & easy.

Mysql DATETIME

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:
datetime_format
This variable is unused.
http://dev.mysql.com/doc/refman/5.0/...variables.html

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 2744
Atli
5,058 Expert 4TB
Hey.

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
By using this site, you agree to our Privacy Policy and Terms of Use.