September 19, 2002 at 6:26 am
Hello,
I have a strange problem using OSQL and isqlW, that I am unable to fix.
If someone could help, it would be great.
run the code at the bottom in the query analyzer in a database,
execute the function with
set language french
select * from fdate2('12/09/2002','SE',0)
result:
2002-09-09 00:00:00.000 2002-09-15 00:00:00.000
it will give you the first and the last date of the current week.
at this point there is no problem
Save the function code in a script file 'the_fn_code.sql',
Drop the function and create it using
osql -E -S (local) -d mydatabase -i c:\the_fn_code.sql
at this point osql does not return me any errors
the run the function with
set language french
select * from fdate2('12/09/2002','SE',0)
result:
it returns me an error
Serveur : Msg 517, Niveau 16, État 1, Procédure FDATE2, Ligne 18
L'ajout d'une valeur à une colonne 'datetime' a provoqué un débordement.
if i translate:
"adding a value to 'datetime' column cause an overflow"
drop the function, and create it by using osql without the script file
like
osql -E -S (local) -d mydatabase
1> create function dbo.fdate2 ......and so on
......
(it is quite boring but i did the test)
run the function with
set language french
select * from fdate2('12/09/2002','SE',0)
result:
2002-09-09 00:00:00.000 2002-09-15 00:00:00.000
THERE IS NO ERRORS !!!!!
I've been working on that since one week. I don't know what i can do.
thanks.....
Here is the function's code:
create FUNCTION [dbo].[FDATE2]
(
@date datetime,
@type char(2),
@dec int
)
RETURNS @TRT table (Dt_Deb datetime,Dt_Fin datetime ) AS
begin
declare @jour char(8)
declare @iddrv int
set @jour=(case when @@language='Français' then 'lundi' else 'Monday' end)
if @type='SE'
begin /*test de pm*/
set @date=DATEADD(dd,7*@dec,@date)
WHILE (DATENAME(dw,@Date) <> @jour)
BEGIN
SET @Date = DATEADD(dd,-1,@Date)
END
insert into @TRT (Dt_Deb,Dt_Fin) values (convert(char(10),@date,103),
convert(char(10),dateadd(dd,6,@date),103))
end
return
end
go
September 19, 2002 at 6:53 am
I think your problem is to do with the way SQL Server is interpreting dates, i.e. it is interpreting them as US format (mm/dd/yyyy) instead of European (dd/mm/yyyy).
Executing a SET DATEFORMAT DMY before calling the function may solve the problem.
September 19, 2002 at 7:15 am
using set dateformat dmy does not solve the pb.
And my pb is to understand why the function
is ok when it is create with:
1) isqlw
2) osql (when you type it without using the -i InputSqlFile)
It is not ok when you create it with
OSQL -E -S (local) -i c:\MySqlScript.sql
(Even if osql does not return any error while it execute the script file)
September 20, 2002 at 1:54 am
I know why, using the profiler.
In fact, then query analyzer can translate the 'ç' from 'Français' in the right code, but osql says that 'ç'='p'
so the line @@language='Français' becomes @@language='Franpais'
when you type directly the line in osql
'ç'='ç' (without using the script file)
Bye
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply