Page 1 of 1

[SOLVED] date time datatype selecting error (MSSQL)

Posted: Mon Oct 09, 2006 3:09 am
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

Posted: Mon Oct 09, 2006 3:30 am
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.

Posted: Mon Oct 09, 2006 3:39 am
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?

Posted: Mon Oct 09, 2006 4:16 am
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