date time format question
-
Our product supports multiple languages.
We have a proc that has 2 input varchar parameters, that are actually date time data. The Cast function works in English but not in French. I want to use the CONVERT function instead of CAST. On French OS, Here are the values that the code is sending. what is the convert format that I should use to make it work with all languages.
exec Palm600.dbo.RPT_GET_STAFF_CALL_DATA N'2005/10/27', N'2005/11/29',N'Both'
Here is the proc txt.
Alter PROC RPT_GET_STAFF_CALL_DATA
@FDate nvarchar(25), @FTDate nvarchar(25), @Inactive nvarchar(8)
as
SET NOCOUNT ON
declare @FDTE datetime, @TDTE datetime
set @FDTE = (cast(@FDate as datetime))
set @TDTE = (cast(@FTDate as datetime)+1)
declare @inac int
set @inac =
(case when @Inactive = 'Active' then 1
when @Inactive = 'Inactive' then 0
when @Inactive = 'Both' then -1 end 
Select
"SEQUENCE",
"FNAME",
"NAME",
("FNAME" + ' ' + "NAME") as
"Full Name",
(SELECT count("SEQUENCE")
FROM "_SMDBA_"."_TELMASTE_" as INC
WHERE INC."OPEN BY" = "_SMDBA_"."_PERSONNEL_"."SEQUENCE"
and "DATE OPEN" >= @FDTE
and "DATE OPEN" < @TDTE) as
"Opened Calls",
(SELECT count("SEQUENCE")
FROM "_SMDBA_"."_TELMASTE_" as INC
WHERE INC."OPEN BY" = "_SMDBA_"."_PERSONNEL_"."SEQUENCE"
and datediff(year,"DATE OPEN",getdate()) = 0) as
"Opened Calls YTD",
(SELECT count("SEQUENCE")
FROM "_SMDBA_"."_WORKORD_" as WO
WHERE WO."OPEN BY" = "_SMDBA_"."_PERSONNEL_"."SEQUENCE"
and "DATE OPEN" >= @FDTE
and "DATE OPEN" < @TDTE) as
"Opened WO's",
(SELECT count("SEQUENCE")
FROM "_SMDBA_"."_WORKORD_" as WO
WHERE WO."OPEN BY" = "_SMDBA_"."_PERSONNEL_"."SEQUENCE"
and datediff(year,"DATE OPEN",getdate()) = 0) as
"Opened WOS YTD",
(SELECT count("SEQUENCE")
FROM "_SMDBA_"."_TELMASTE_" as INC
WHERE INC."CLOSED BY" = "_SMDBA_"."_PERSONNEL_"."SEQUENCE"
and "CLOSED ON" >= @FDTE
and "DATE OPEN" < @TDTE) as
"Closed Calls",
(SELECT count("SEQUENCE")
FROM "_SMDBA_"."_TELMASTE_" as INC
WHERE INC."CLOSED BY" = "_SMDBA_"."_PERSONNEL_"."SEQUENCE"
and datediff(year,"CLOSED ON",getdate()) = 0) as
"Closed Calls YTD",
(SELECT count("SEQUENCE") FROM "_SMDBA_"."_WORKORD_" as WO
WHERE WO."CLOSED BY" = "_SMDBA_"."_PERSONNEL_"."SEQUENCE"
and "CLOSED ON" >= @FDTE and "DATE OPEN" < @TDTE)
as "Closed WO's",
(SELECT count("SEQUENCE") FROM "_SMDBA_"."_WORKORD_" as WO
WHERE WO."CLOSED BY" = "_SMDBA_"."_PERSONNEL_"."SEQUENCE"
and datediff(year,"CLOSED ON",getdate()) = 0)
as "Closed WOS YTD",
(SELECT count("SEQUENCE") FROM "_SMDBA_"."_TELMASTE_" WHERE
"DATE OPEN" >= @FDTE and "DATE OPEN" < @TDTE)
as TOT_Calls_DRNGE,
(SELECT count("SEQUENCE") FROM "_SMDBA_"."_TELMASTE_" WHERE datediff(year,"DATE OPEN",getdate()) = 0)
as TOT_Calls_YTD,
(SELECT count("SEQUENCE") FROM "_SMDBA_"."_WORKORD_" WHERE
"DATE OPEN" >= @FDTE and "DATE OPEN" < @TDTE)
as TOT_WOS_DRNGE,
(SELECT count("SEQUENCE") FROM "_SMDBA_"."_WORKORD_" WHERE datediff(year,"DATE OPEN",getdate()) = 0)
as TOT_WOS_YTD,
@FDTE as "From Date",@TDTE as "To Date",
"_INACTIVE_:"
FROM "_SMDBA_"."_PERSONNEL_"
WHERE "_INACTIVE_:" <> @inac
order by "Full Name"
GO
-
Since the stored procedure is not being passed the date format, there is no way to determine how to convert.
For example, if being passed '2005/10/12', is this the 10th day of December or the 12th day of October?
Recommend that the stored procedure be passed datetime datatype parameters and all conversions be performed on the client.
SQL = Scarcely Qualifies as a Language
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply