Posted by iulian.ilea on 10/30/06 10:45

Colin Fine wrote:
> iulian.ilea wrote:
> > 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.
> >
> 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.
>
> > 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.
> >
> So it was a PHP problem them?
>
> Colin

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?

[Back to original message]