date problem

[iulian.ilea]

Hello,

I store a date ( format mm/dd/yyyy ) on MsSQL in a datetime field. In
DB is stored correctly, same format, but when I retrieve it I get
dd/mm/yyyy. You have any ideea why this is happening?

[Daz]

Re: date problem


iulian.ilea wrote:

Hello,
>
I store a date ( format mm/dd/yyyy ) on MsSQL in a datetime field. In
DB is stored correctly, same format, but when I retrieve it I get
dd/mm/yyyy. You have any ideea why this is happening?

As far as I know, the date should be inseted as yyyy-mm-dd, stored in
this format, and also retreived in this format.

[Daz]

Re: date problem


iulian.ilea wrote:

Hello,
>
I store a date ( format mm/dd/yyyy ) on MsSQL in a datetime field. In
DB is stored correctly, same format, but when I retrieve it I get
dd/mm/yyyy. You have any ideea why this is happening?

I would suggest having a look at the date through CLI. The interface
you may be using could be possibly changing the format of the output?

Here's what I get:

mysqlCREATE TABLE test2 (`date` date);
Query OK, 0 rows affected (0.00 sec)

mysqlINSERT INTO test2 SET date='2006-02-01';
Query OK, 0 rows affected (0.00 sec)

mysqlSELECT * FROM test2 WHERE 1;
+------------+
| date |
+------------+
| 2006-02-01 |
+------------+
Query OK, 0 rows affected (0.00 sec)

[Daz]

Re: date problem


Daz wrote:

iulian.ilea wrote:

Hello,

I store a date ( format mm/dd/yyyy ) on MsSQL in a datetime field. In
DB is stored correctly, same format, but when I retrieve it I get
dd/mm/yyyy. You have any ideea why this is happening?

>
I would suggest having a look at the date through CLI. The interface
you may be using could be possibly changing the format of the output?
>
Here's what I get:
>
mysqlCREATE TABLE test2 (`date` date);
Query OK, 0 rows affected (0.00 sec)
>
mysqlINSERT INTO test2 SET date='2006-02-01';
Query OK, 0 rows affected (0.00 sec)
>
mysqlSELECT * FROM test2 WHERE 1;
+------------+
| date |
+------------+
| 2006-02-01 |
+------------+
Query OK, 0 rows affected (0.00 sec)

Ugh... Only there were rows affected... I just copied the same one
three times...

[Daz]

Re: date problem


Daz wrote:

Daz wrote:

iulian.ilea wrote:

Hello,
>
I store a date ( format mm/dd/yyyy ) on MsSQL in a datetime field. In
DB is stored correctly, same format, but when I retrieve it I get
dd/mm/yyyy. You have any ideea why this is happening?

I would suggest having a look at the date through CLI. The interface
you may be using could be possibly changing the format of the output?

Here's what I get:

mysqlCREATE TABLE test2 (`date` date);
Query OK, 0 rows affected (0.00 sec)

mysqlINSERT INTO test2 SET date='2006-02-01';
Query OK, 0 rows affected (0.00 sec)

mysqlSELECT * FROM test2 WHERE 1;
+------------+
| date |
+------------+
| 2006-02-01 |
+------------+
Query OK, 0 rows affected (0.00 sec)

>
Ugh... Only there were rows affected... I just copied the same one
three times...

Please excuse my whilst I take my foot out of my mouth. I read MySQL,
and it actually said MsSQL. I would recommend you use a real DMS, if
possible (just my opinion). And maybe try posting in another group more
MsSQL oriented, as it sounds like it it could be a fault with the
database.

My answers were off-topic, but I thought I could help.

Sorry.

[Pedro Graca]

Re: date problem

[ crossposted by me to comp.databases. mysql, followups set ]

iulian.ilea wrote:

I store a date ( format mm/dd/yyyy ) on MsSQL in a datetime field. In
DB is stored correctly, same format, but when I retrieve it I get
dd/mm/yyyy. You have any ideea why this is happening?

My MySQL does no accept dates in that format

mysqlcreate table xx (xx_id int(1), d datetime);
Query OK, 0 rows affected (0.01 sec)

mysqlinsert xx values
-(1, 10/08/2006), (2, '10/08/2006'),
-(3, 2006-10-08), (4, '2006-10-08'),
-(5, 10082006), (6, 20061008);
Query OK, 6 rows affected, 4 warnings (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 4

mysqlselect * from xx;
+-------+---------------------+
| xx_id | d |
+-------+---------------------+
| 1 | 0000-00-00 00:00:00 |
| 2 | 0000-00-00 00:00:00 |
| 3 | 0000-00-00 00:00:00 |
| 4 | 2006-10-08 00:00:00 |
| 5 | 0000-00-00 00:00:00 |
| 6 | 2006-10-08 00:00:00 |
+-------+---------------------+
4 rows in set (0.00 sec)

mysqldrop table xx;
Query OK, 0 rows affected (0.00 sec)

MySQL *always* interprets dates as YYYY-MM-DD
Check the MySQL manual
http://dev.mysql.com/doc/refman/5.0/...ime-types.html

--
File not found: (R)esume, (R)etry, (R)erun, (R)eturn, (R)eboot

[Todd]

Re: date problem

Can't give you the exact why, but you can fix it in your SQL Select
statement.

"SELECT CONVERT(VARCHAR , yourDateField, 101) FROM yourTable"

Will return your date value in the mm/dd/yyyy format.

See http://msdn2.microsoft.com/en-us/library/ms187928.aspx

For more details.

[iulian.ilea]

Re: date problem

Thanks all for your replyes.

I can't fix it from SELECT because I have SELECT * FROM... I have more
than one field with datetime type, and more than one table that uses
the same sql statement only customed after the table name.

example:
table1:
field1 - int
field2 - datetime
field3 - int
....

table2
field1 - datetime
field2 - int
....

So I don't know from what table is selecting to write SELECT field1,
field2, other_fields from table1. It's generical, it's a kernel who
generates HTML code based on several sql statements.

I don't know if I made me clear enough, I hope so.

But, the problem is when I retrieve value from DB. Because is stored
correctly there. I have no clue about what can be the problem.

It's the same ini file. I looked over phpinfo() on both servers and on
the one with problem I have this: Default timezone Europe/Paris. The
other one hase Europe/Helsinky. You thin this can be a problem? How can
it be fixed?

Todd wrote:

Can't give you the exact why, but you can fix it in your SQL Select
statement.
>
"SELECT CONVERT(VARCHAR , yourDateField, 101) FROM yourTable"
>
Will return your date value in the mm/dd/yyyy format.
>
See http://msdn2.microsoft.com/en-us/library/ms187928.aspx
>
For more details.

[Todd]

Re: date problem

The problem with your situation is that the dates are not stored in SQL
Server in strings like mm/dd/yyyy or dd/mm/yyy. The dates are stored as the
number of days before or after 1/1/1900. Times are stored in a similar
manner.

See http://msdn2.microsoft.com/en-us/library/ms187819.aspx

I suppose you could write some form of function that does a regexp check and
looks for date values on returned fields and then re-writes the date fields
in the format you want. This is an UGLY hack and would cause some overhead,
but it could work. The other option is that you could store your dates in a
char field and then you will get them back the way you want. Your would
have to do some checking on the date fields when entered to make sure they
are valid.

I don't know if either or these would work for you or not.

Good luck!

[Todd]

Re: date problem

.... Or if your database isn't too complicated, you could create a view for
each table using the CONVERT function on your date fields. Your select
statement could then be done on the view rather than the table directly.
This would return a character representation of your date field in the
format you want.

[Pedro Graca]

Re: date problem

Daz wrote:

Please excuse my whilst I take my foot out of my mouth. I read MySQL,
and it actually said MsSQL.

Same here.
Sorry.

To the OP: write MSSQL or MySQL instead of MsSQL or MYSQL to avoid
future confusions.

--
File not found: (R)esume, (R)etry, (R)erun, (R)eturn, (R)eboot

[iulian.ilea]

Re: date problem

The problem persists even after I modified default timezone in php.ini
: set to Europe/Helsinki. This was the only difference.