Posted by iulian.ilea on 10/25/06 12:56

Jerry Stuckle wrote:
> 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
> >
>
> I think it should work. But it's a lot of unnecessary complication and
> work.
>
> 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.
> jstucklex@attglobal.net
> ==================

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.

[Back to original message]