datetime problem

[iulian.ilea]

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

[Captain Paralytic]

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.

[Jerry Stuckle]

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.
js*******@attglobal.net
==================

[iulian.ilea]

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.
js*******@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.

[Colin Fine]

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.
js*******@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

[iulian.ilea]

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.
js*******@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?