Why working in SQL 7 but not under SQL 2000? 2

[Niavlys]

Hi,
I'm a bit desperate. I have this query :

SELECT GPM_E_ABS.FICHE " &_
"FROM ((GPM_E_DAN INNER JOIN (GPM_E_ABS INNER JOIN GPM_T_ECO ON GPM_E_ABS.ID_ECO = GPM_T_ECO.ID_ECO) ON (GPM_E_DAN.ID_ECO = GPM_T_ECO.ID_ECO) " &_
"AND (GPM_E_DAN.FICHE = GPM_E_ABS.FICHE) AND (GPM_E_DAN.ORG = GPM_T_ECO.ORG)) INNER JOIN GPM_E_ELE ON GPM_E_DAN.FICHE = GPM_E_ELE.FICHE) " &_
"INNER JOIN GPM_T_MOTIF_ABS ON (GPM_E_ABS.MOTIF_ABS = GPM_T_MOTIF_ABS.MOTIF_ABS) AND (GPM_T_ECO.ID_ECO = GPM_T_MOTIF_ABS.ID_ECO) " &_
 "GROUP BY GPM_E_ABS.FICHE, GPM_E_ABS.DATE_ABS, GPM_T_ECO.ECO, GPM_T_ECO.ANNEE, GPM_E_ELE.NOM, GPM_E_DAN.CLASSE, GPM_T_MOTIF_ABS.DESCR " &_

"HAVING (((GPM_E_ABS.DATE_ABS)='" & date() & "') AND 

((GPM_T_ECO.ECO)='" & schoolNum & "') AND ((GPM_T_ECO.ANNEE)=" & schoolYear() & ")) " &_
"ORDER BY GPM_E_ELE.NOM;"

that was working fine with SQL 7. I upgraded my server to SQL 2000 and now I get this annoying error: Microsoft OLE DB Provider for SQL Server error '80040e07'

The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.

I'm having problems with this HAVING (((GPM_E_ABS.DATE_ABS)='" & date() & "')
I tried using CAST or CONVERT but could not make it work. I red a lot of post about this error but I can't figure out the syntax.

Can someone help me?
Thanks

 

[vongrunt]

> I'm having problems with this HAVING (((GPM_E_ABS.DATE_ABS)='" & date() & "')

Argh...

server probably expects date string in different format.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[mrdenny]

An out-of-range datetime usualy means that the year is to high or to low.

What date are you passing in?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Niavlys]

I'm sending the result of the vb date() function that is something like '04/24/2006'. Is the date function should return the date in the Regional settings format?

Thanks!

 

[vongrunt]

> Is the date function should return the date in the Regional settings format?

The safest shot is to send date string literals in unseparated (ISO) format - yyyymmdd (hh:mm:ss). Then code execution won't depend on regional settings anywhere.


------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[mrdenny]

If all you are passing in is the current date, why not get the current date from the SQL Server. This takes the VB code out of the equation all to gether.

"HAVING (((GPM_E_ABS.DATE_ABS)='convert(datetime, convert(varchar(10), getdate(), 101))') AND

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[vongrunt]

True absolutely... but without single quotes

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[Niavlys]

Thank you very much guys for your help, it worked!

 

[mrdenny]

whoops, my bad. Thanks vongrunt.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com