comparing DateTime in UK Format

Hello friends,

I am trying to return all records between 2 dates. The Date columns are in DateTime format, and i am ignoring the timestamp. The user should be able to input UK Date Format (dd/mm/yyyy) and return the rows. This sql code works fine for American date format, but i get an error: converting from varchar to datetime when i put in a UK format. eg. 22/11/06. Please advise on this problem! many thanks!

ALTER PROCEDURE SalaryBetweenDates
(

@.WeekStart datetime,

@.WeekEnd datetime
)
AS

BEGIN
SET @.WeekStart = (SELECT REPLACE(CONVERT(DATETIME,@.WeekStart ,103),' ','-'))
SET @.WeekEnd = (SELECT REPLACE(CONVERT(DATETIME,@.WeekEnd ,103),' ','-'))
END

BEGIN
SELECT s.StaffNo,s.StaffName,s.StaffAddress, s.HourlyRate,
sh.HoursWorked, CONVERT(varchar(12), sh.WeekStart, 103) AS StartDate, CONVERT(varchar(12), sh.WeekEnd, 103)As EndDate,(sh.HoursWorked * s.HourlyRate)"Salary"
From Staff As S INNER JOIN StaffHours As Sh
On S.StaffNo = Sh.StaffNo
WHERE sh.WeekStart >= (@.WeekStart)
AND sh.WeekEnd <= (@.WeekEnd)

FOR XML RAW ('paySlip'), root('Staff'), ELEMENTS XSINIL
END

ReturnYou need to convert the UK format date into a format that Sql can read.
I always use the following ones
'YYYY-MM-DD' for date
'YYYY-MM-DD HH:NN:SS' for date & time
use exactly as is... don't change the sperators

so '22/11/06' should be passed to sqlserver as '2006-11-22'|||

If you want to be able to call the procedure like this

EXEC SalaryBetweenDates '22/11/06', '1/12/06'

you're going to have to make the procedure parameters varchars and write some string handling code to figure out the strings that are passed in. I'd recommend that you leave it as it is and have the application pass dates in the format that SQL Server expects, if necessary have the application do the work at figuring out what date the user actually entered.

|||

thanks for your help guys. I set the parameters as strings in the end, and used REPLACE(CONVERT) to handle the function

:-)