I agree. The date calendar prompt thinggummyjig is a damned nuisance. And the convert function supplied by a previous post puts the date in mm/dd/yyyy which is US not UK version.
select convert(varchar, getdate(),101)
06/17/2010
I'm sure there is another format code to get it in the UK version but personally I normally select the date in my stored procedure using one of these user-friendly formats listed below to retrieve the date in varchar (i.e. SQL Server equivalent of string )format and then simply get the CR user to type in the date in that format e.g. 2008-08-17 also, make the CR parameter a string also and then you're comparing like with like.
Too bad you can't use the funky date picker calendar - you probably can if you set the input parameter to Date and play around with CR formulas for a bit.
User Friendly Format 1
=====================
Select TOP 15 CAST(DATEPART(yyyy, M.mopendt) as varchar(4))+ '-'
+ RIGHT ('00' + CAST(DATEPART(mm, M.mopendt) as varchar(2)),2) + '-'
+ RIGHT ('00' + CAST(DATEPART(dd, M.mopendt) as varchar(2)),2)
from matter M
1981-12-17
1985-08-21
1998-05-26
1981-09-21
User Friendly Format 2
=======================
Select TOP 15
RIGHT ('00' + CAST(DATEPART(dd, M.mopendt) as varchar(2)),2) + '/'
+ RIGHT ('00' + CAST(DATEPART(mm, M.mopendt) as varchar(2)),2) + '/'
+ CAST(DATEPART(yyyy, M.mopendt) as varchar(4))
from matter M
17/12/1981
21/08/1985
26/05/1998
21/09/1981
User Friendly Format 3 (for display on the CR report)
========================
select top 15
CASE
WHEN datepart(dd,M.mopendt) IN (1, 21, 31) then RIGHT('0' + CAST(datepart(dd,M.mopendt) as varchar(2)),2) + 'st ' + DATENAME(month,M.mopendt)+ ' ' + CAST(datepart(yyyy,M.mopendt) as varchar(4))
WHEN datepart(dd,M.mopendt) IN (2, 22) then RIGHT('0' + CAST(datepart(dd,M.mopendt) as varchar(2)),2) + 'nd ' + DATENAME(month,M.mopendt)+ ' ' + CAST(datepart(yyyy,M.mopendt) as varchar(4))
WHEN datepart(dd,M.mopendt) IN (3, 23) then RIGHT('0' + CAST(datepart(dd,M.mopendt) as varchar(2)),2) + 'rd ' + DATENAME(month,M.mopendt)+ ' ' + CAST(datepart(yyyy,M.mopendt) as varchar(4))
ELSE RIGHT('0' + CAST(datepart(dd,M.mopendt) as varchar(2)),2) + 'th ' + DATENAME(month,M.mopendt)+ ' ' + CAST(datepart(yyyy,M.mopendt) as varchar(4))
END
as friendly_mopendt_ver1 from Matter M
--sample
17th December 1981
21st August 1985
26th May 1998
21st September 1981