Re: DATEFORMAT

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 04/27/04 

Date: Tue, 27 Apr 2004 17:36:13 +0200

Your best bet is to let the parameters to the stored procedure be datetime, i.e., no conversion at all in the
stored procedure. If you use parameters in the proc, SQL Server will determine selectivity based on the value
a param has when the proc plan is created. If you use variables inside the proc, then the optimizer doesn't
know the value, so it determines selectivity based on hard-wired rules (like 10= for equality search, 30% for
gt or or etc, I don't remember the values off the top of my head). 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Konstantinos Michas" <anonymous@discussions.microsoft.com> wrote in message
news:4f3901c42c6a$7269fa40$a001280a@phx.gbl...
>
> Hi Tibor, thanks for answering. I don't use it, but I was
> thinking about to use it (instead of using a convert for
> each date type variable). Using Convert only for
> variables and not for my columns, will statements look
> and use for Indexes?
>
> Thanks in advance.
>
> >-----Original Message-----
> >Even worse. It will be cached, but each execution will
> cause a recompile when SQL Server hits the SET command.
> >Why not use a safe dateformat instead? Or even pass the
> values in as datetime datatypes?`
> >
> >http://www.karaszi.com/sqlserver/info_datetime.asp
> >http://www.karaszi.com/sqlserver/info_sp_recompile_set.as
> p
> >
> >-- 
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >
> >
> >"Konstantinos Michas"
> <anonymous@discussions.microsoft.com> wrote in message
> >news:4f2301c42c63$a75bc410$a101280a@phx.gbl...
> >> Hello Experts,
> >>
> >> If I use the SET DATEFORMAT statement in a stored
> >> procedure (sp) then every time my sp runs that will
> cause
> >> a compile and will never cached. I'm I right?
> >>
> >> Thanks in advance!
> >
> >
> >.
> >

Relevant Pages

  • Re: Using CASE .. WHEN to have dynamic sort
    ... > I'm trying to create a Stored Procedure that returns a recordset, ... > The conversion of a char data type to a datetime data type resulted in ... > an out-of-range datetime value. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: between dates with no time consideration
    ... > Columnist, SQL Server Professional ... > declare @start as datetime ... >> only passing the date value back to the stored procedure, ...
    (microsoft.public.sqlserver.programming)
  • Re: Want to different types of table types in sql server2000!!!
    ... And are you using this index in the stored procedure? ... But SQL Server doesn't create statistics for TVs, ... > Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Server 2000/Access 2000 datetime conversion error
    ... Microsoft Access Developer's Guide to SQL Server ... >I can't get my stored procedure to execute from MS Access ... >without throwing a datetime conversion error. ...
    (microsoft.public.sqlserver.odbc)
  • SQL Server 2000/Access 2000 datetime conversion error
    ... I can't get my stored procedure to execute from MS Access ... without throwing a datetime conversion error. ... connection to the SQL Server database is over an ODBC ...
    (microsoft.public.sqlserver.odbc)