datetime problem

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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

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

Re: datetime problem

iulian.ilea wrote:
Quoted text here. Click to load it

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.

Re: datetime problem

iulian.ilea wrote:
Quoted text here. Click to load it

I think it should work.  But it's a lot of unnecessary complication and  

Fix your problem with the datetime column.  You might try  
alt.databases.mssql for some help on it.

Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.

Re: datetime problem

Jerry Stuckle wrote:
Quoted text here. Click to load it

Captain Paralytic, this is a PHP group so, obviously that is ask
questions PHP related. I use MSSQL not MySQL. Yes, the entire server
machine breaks because of an simple SQL select statement. Are you
kidding, right? The problem was on the select, that is why I asked if
is correct to do this.

Jerry, it works ok now that I discovered the problemm.

The problem:
I had Remote Desktop Connection this afternoon to the machine with
problems. I checked if all fields that were datetime in the past are
now varchar and all was ok. The problem was only on one table that
stores data (and I had 5 tables that are using the same code so I
thought to delete all record from that table). I delete all records and
it works now. Maybe the 'created' field from table stored data of type
datetime and that caused this problem on convert.

Re: datetime problem

iulian.ilea wrote:
Quoted text here. Click to load it
Just have a look through the message headers and you will see how many  
people post problems here that are actually about HTML, server  
configuration or database and nothing to do with PHP.

You made no explicit reference to PHP, and you used the phrase that all  
support engineers hate: 'is not working' (or the more common variant  
'doesn't work').

Captain Paralytic made some suggestions, and invited you to clarify your  
problem, and you responded with sarcasm.

Quoted text here. Click to load it
So it was a PHP problem them?


Re: datetime problem

Colin Fine wrote:
Quoted text here. Click to load it

I don't want to comment anything you wrote above because I think it has
no sense. But I will answer to last question: like I wrote in my first
post I tried to change default timezone and I couldn't. In fact I could
change it but I had the same problem. That is why I choose to have a
varchar field type instead of datetime and make those sql conversions.
I think it was more a sql problem. But it still remains the mystery :)
- why I got the same error even if I changed default timezone?

Site Timeline