converting datetime for comparison

SQL Apprentice Question
I am passing a datetime to a stored proc and I need to convert this
datetime to the format dd/mm/yyyy and then make a comparison between it
and a field in my select statement. Can somebody tell me what I am
doing wrong in my code?

declare @Company varchar(50)
declare @SerialNumber varchar(50)
declare @ProductGroup varchar(10)
declare @InstallationDate datetime


set @Company = ''
set @SerialNumber = ''
set @ProductGroup = '9'
set @InstallationDate = '21/03/2003 00:00:00'


select p.ProductID, c.Competitor, m.Machine, s.[name], p.SerialNumber,
p.InstallationDate,
t.ProductTypeID, t.[Description], convert(datetime,
left(p.InstallationDate, 11), 103),
convert(datetime, left(@InstallationDate, 11), 103)
from Products p
inner join companysite s
on p.OwnedByCompanyID = s.CompanySiteKey
inner join Competitors c
on c.CompetitorID = p.ProducedByID
inner join CompetitorMachines m
on p.MachineID = m.CompetitorMachineID
inner join ProductTypes t
on t.ProductTypeID = m.ProductTypeID
where s.[name] like '%' + @Company + '%'
and p.SerialNumber like '%' + @SerialNumber + '%'
and t.ProductTypeID like '%' + @ProductGroup + '%'
and convert(char(11), left(p.InstallationDate, 11), 103) =
convert(char(11), left(@InstallationDate, 11), 103)



Celko Answers

>> I am passing a datetime to a stored proc and I need to convert this datetime to the format dd/mm/yyyy and then make a comparison between it and a field [sic] in my select statement. <<


Why? You can compare temporal data types directly to each other
without having to convert them to strings. You still think you are
working with a field, like COBOL or other non-SQL languages. SQL has
columns; columns have a data type and constraints with no implied
PHYSICAL format. Totally different concept!

If you want to store just dates in SQL Server, you need to add a
constraint to the DDL that makes sure the DATETIME is shown as midnight
(i.e. with 00:00:00 hours). Then you code in such a way as assume that
this means the whole day from 00:00:00 to 23:59:59.997 by using BETWEEN
predicates of by setting the hours to midnight.


SQL is a language meant for data, not for procedural coding and display
work. Look for a data solution first.


You might also want to learn to use ISO-8601 date formats, how to name
data elements and pick data types. For example, do you really have a
CHAR(50) company name? That kind of non-planning invites garbage data.


Since you did not properly declare the first parameter, I am assuming
it was supposed to be a name; ditto for a lot of your other data
elements -- description of what? Name of what?


There is no such thing as a "-TypeID"; either the data element is an
identifer for a particular entity in the data model, or it is a value
that represents a type; it cannot be both.


It looks like you have two names for the same data element -- is it a
company_id or site_key?