|
|
Posted by Captain Paralytic on 10/25/06 09:54
iulian.ilea wrote:
> Is correct to have a varchar field and insert dates of type dd/mm/yyyy
> into it? I choose this method because I have an application that runs
> on more than one server. So, if I used a datetime field (MSSQL Server)
> it worked on my test machine. If I run the same application on another
> machine with different regional settings is not working. I tried with
> date_default_timezone_set to change timezone but is not changing. After
> date_default_timezone_get the result was the new timezone but the dates
> were the same. So, after that I changed all datetime fields into
> varchar and used query's like this (see bellow) where I have date
> operations.
>
> My question: is correct to to this? Because is working on one machine
> and on another one I get an error that it can't convert char to
> datetime. My guess is that I left a datetime field unchanged or my
> ideea about using varchar instead of datetime is completely wrong.
>
> SELECT * FROM DocD WHERE CONVERT(datetime,created,101) BETWEEN
> CONVERT(datetime,'10/25/2006',101) AND
> CONVERT(datetime,'10/25/2006',101) ORDER BY cName ASC,
> CONVERT(datetime,created,101) DESC, DocID DESC
Well, first of all, if you are storing a string that is always exactly
the same length (in this case 10 characters) then it should be in a
fixed length CHAR field rather than a VARCHAR one.
Next, you make a statement "If I run the same application on another
machine with different regional settings is not working.".
This is probably the most important piece of information you could have
supplied in this question, but all you have said is that it doesn't
work! Does the whole server break? Does the MySQL instance crash? Does
any code (php, asp, perl, ...) used to access the database crash? Does
the date get produced but in the wrong format? Does the SQL simply
return an SQL error? ...
I could go on! You need to tell us WHAT the problem is!
Then we might be able to help you solve it.
Navigation:
[Reply to this message]
|