[SOLVED] date time datatype selecting error (MSSQL)

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ryuuka
Forum Contributor
Posts: 128
Joined: Tue Sep 05, 2006 8:18 am
Location: the netherlands

[SOLVED] date time datatype selecting error (MSSQL)

Post by ryuuka »

hello again i keep getting this problem:

[Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type
resulted in an out-of-range datetime value

in my mssql database. why this happens i do not know. i read up on the internet that
this may have something to do with the time/language settings. Unfortunatly these can't be changed because i'm not the only one that makes use of this DB and table

my query

Code: Select all

SELECT naam, MAX(totaal) - MIN(totaal) AS complete, datum
FROM @@@@@_printer_stats
WHERE (datum >= '1-8-2006') AND (datum <= '31-8-2006')
GROUP BY naam, datum
The date in the table is in a datetime format with a length of 8 and it's a PK
The date in that is in the table is exactly like in the table: 1-8-2006

what i need for it to do is select everything between 1-8-2006 and 31-8-2006

i can't change the datetime format in the table because it's also being used for something else
and in the light of this the language also can't be changed
Last edited by ryuuka on Mon Oct 09, 2006 4:17 am, edited 1 time in total.
chakhar86
Forum Commoner
Posts: 45
Joined: Mon Jun 05, 2006 1:36 am
Contact:

Post by chakhar86 »

Don't like MSSQL, but most DBMS use ISO standar dateformat "yyyy-mm-dd".
so use it instead "dd-mm-yyyy'.
I've encountered this problem when I use ODBC with MS ACCESS database, and only find that solution works.

If you don't want to change date format, try strtotime() function at your own risk
strtotime($str_date); => will return integer value of the $str_date using it own translation of date.
chakhar86
Forum Commoner
Posts: 45
Joined: Mon Jun 05, 2006 1:36 am
Contact:

Post by chakhar86 »

addition:

the date format you use in MSSQL is only for view. In the machine it's still recorded as 'yyyy-mm-dd'
try it yourself :

execute this query :
$SQL = "SELECT date_field FROM the_table";
$result = odbc_do($SQL);
$date_var = odbc_fetch_array($result, 1);

and print the preformatted one:

echo $date_var['date_field'];


so?
ryuuka
Forum Contributor
Posts: 128
Joined: Tue Sep 05, 2006 8:18 am
Location: the netherlands

Post by ryuuka »

this worked thanks man

Code: Select all

SELECT naam, MAX(totaal) - MIN(totaal) AS complete
                            FROM @@@@@_printer_stats
                            WHERE     (datum >= '2006-8-1') AND (datum <= '2006-8-31')
                            GROUP BY naam
it was the dateformat
thanks again
Post Reply