Re: Passing DateDiff rather than Actual Dates as Parameters

laurenq uantrell (laurenquantrell@xxxxxxxxxxx) writes:
> Erland,
> Why pass it as a string and then convert it to a date? In your example
> you're passing the formatted date (Format(Date, "YYYYMMDD") ) as a
> string to the varchar parameter, then converting it to a date in
> @real_date? Why not just pass it to a datetime parameter in the first
> place and have the error reporting of the client deal with formatting
> problems rather than the vague-aries of SQL error reporting?
>
> @indate varchar(10)
> AS
> Declare @real_date datetime
> Select @real_date = Convert(datetime, @indate, 112)

Well, it wasn't me who suggested that, and I entirely agree with you.
Passing datetime values as strings is a poor idea.




--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Failed to convert parameter value from a String to a DateTime
    ... perhaps the error occurs because SQL Server defaults to nvarchar when a parameter is NULL. ... I would try to declare the parameter as DateTime before to assign it the DBNull.Value. ... value may be a valid date such as '01/01/2007' or a null string. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Index performance revisited
    ... but could not find the proper ... I was trying to differentiate between a constant (like a string), ... In SQL Server, as well as the SQL standard, you do indeed ... specify datetime data as a string. ...
    (microsoft.public.sqlserver.programming)
  • Re: datetime to datetime offset
    ... Roger writes: ... Internet and convert our dateTime values to a string, ... SQL Server, but there are string literals, and string literals can be ...
    (microsoft.public.sqlserver.server)
  • Re: Problem inserting dates to SQL Server
    ... The conversion of a char data type to a datetime data type resulted in ... an out-of-range datetime value. ... Are you passing the parameter in as string or as DateTime. ... But you are saying the error is still in around conversion, which indicates to me that you are passing a string into SQL server and hoping it can parse that into the correct type. ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Format a datetime columns output?
    ... I know you know this Erland, ... it's just another string for the application. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)