SQL DATE Problem

Hello Experts,

Got a SQL Server table and have a "Datetime" field that I want to be use in a Select statement with only a "Date" check.

I am on the fence about using either a Cast or Convert.

Select * from tblName
WHERE CAST(date, HireDate) = '2005-03-19"


Select * from tblName
WHERE CONVERT(date, HireDate) = '2005-03-19"

Date gives me yyyy-mm-dd format for the comparison against the HireDate.

What is the real difference between the two and which is needed here?  I say CAST, as it only applies to the Select and change/convert anything (whatever it is)


Bob BenderAsked:
Who is Participating?
TempDBAConnect With a Mentor Commented:
Both are fine and you can use both. Here is some difference between them

Because SQL Server provides both functions, there may be some confusion about which is best to use and under what circumstances. CONVERT is specific to SQL Server, and allows for a greater breadth of flexibility when converting between date and time values, fractional numbers, and monetary signifiers.

CAST is the more ANSI-standard of the two functions, meaning that while it's more portable (i.e., a function that uses CAST can be used in other database applications more or less as-is), it's also less powerful. CAST is also required when converting between decimal and numeric values to preserve the number of decimal places in the original expression. For those reasons, it's best to use CAST first, unless there is some specific thing that only CONVERT can provide.
CONVERT is SQL Server specific and CAST is ANSII.. if that helps any.
Bob BenderAuthor Commented:
Still odd that both basically do the same thing.  I would think using cast would be better, as there are NO decimals places or accuracy needed cause it is a datetime field
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.