datetime problem

**Captain Paralytic** · Oct 25 '06, 09:35 AM

Re: datetime problem

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_ti mezone_set to change timezone but is not changing. After
date_default_ti mezone_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(datetim e,created,101) BETWEEN
CONVERT(datetim e,'10/25/2006',101) AND
CONVERT(datetim e,'10/25/2006',101) ORDER BY cName ASC,
CONVERT(datetim e,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** · Oct 25 '06, 10:25 AM

Re: datetime problem

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_ti mezone_set to change timezone but is not changing. After
date_default_ti mezone_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(datetim e,created,101) BETWEEN
CONVERT(datetim e,'10/25/2006',101) AND
CONVERT(datetim e,'10/25/2006',101) ORDER BY cName ASC,
CONVERT(datetim e,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.m ssql for some help on it.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

**iulian.ilea** · Oct 25 '06, 12:35 PM

Re: datetime problem

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_ti mezone_set to change timezone but is not changing. After
date_default_ti mezone_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(datetim e,created,101) BETWEEN
CONVERT(datetim e,'10/25/2006',101) AND
CONVERT(datetim e,'10/25/2006',101) ORDER BY cName ASC,
CONVERT(datetim e,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.m ssql for some help on it.
>
--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.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** · Oct 29 '06, 05:25 PM

Re: datetime problem

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(datetim e,created,101) BETWEEN
>>CONVERT(datet ime,'10/25/2006',101) AND
>>CONVERT(datet ime,'10/25/2006',101) ORDER BY cName ASC,
>>CONVERT(datet ime,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@attgl obal.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** · Oct 30 '06, 10:25 AM

Re: datetime problem

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_t imezone_set to change timezone but is not changing. After
>date_default_t imezone_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(datetim e,created,101) BETWEEN
>CONVERT(dateti me,'10/25/2006',101) AND
>CONVERT(dateti me,'10/25/2006',101) ORDER BY cName ASC,
>CONVERT(dateti me,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.m ssql for some help on it.
>
--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.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?

datetime problem

datetime problem

Comment

Comment

Comment

Comment

Comment