"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:uXxEcC9DEHA.3404@TK2MSFTNGP10.phx.gbl...
> when building a t-sql string , what is the preferred method to convert a
> datetime parameter to a string ...
>
> i could imagine doing it like this : (MyDateTimeCol is a table column of
> type datetime)
>
> set @sql = @sql + 'MyDateTimeCol = ''' +
> convert(varchar(10),@MyDateTimeParam,101) + ''''
>
> is there a better/preferred way ?
>
>

"Andrew John" <aj@DELETEmistrose.com> wrote in message
news:uYTYRo9DEHA.3716@TK2MSFTNGP10.phx.gbl...
> "John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:uXxEcC9DEHA.3404@TK2MSFTNGP10.phx.gbl...
> > when building a t-sql string , what is the preferred method to convert a
> > datetime parameter to a string ...
> >
> > i could imagine doing it like this : (MyDateTimeCol is a table column
of
> > type datetime)
> >
> > set @sql = @sql + 'MyDateTimeCol = ''' +
> > convert(varchar(10),@MyDateTimeParam,101) + ''''
> >
> > is there a better/preferred way ?
> >
>
> John,
>
> As written, I would have to agree with Aaron. You have used an
> explicit style to convert, so you definitely get a string of the format
you
> specify. But assuming MyDateTimeCol is actually smalldatetime/datetime,
> this string is then IMPLICITLY promoted to a datetime to do the comparison
to
> the column ( check Books Online under data type precedence ), so you are
relying
> on the region settings / date format matching at runtime.
>
> There are 3 methods I have used to do this safely:
>
> 1) Force SQL to do the convert explicitly
>
> set @sql = @sql + ' MyDateTimeCol = convert( datetime, ''' + convert(
varchar(10), @MyDateTimeParam, 101 ) + ''', 101 )'
>
> which is a lot of typing, but handles times safely as well as just dates.
>
> 2) Use the "guaranteed" format:
>
> set @sql = @sql + ' MyDateTimeCol = ''' + convert( varchar(10),
@MyDateTimeParam, 112 ) + ''''
>
> 3) Don't use dyanamic SQL !
>
> Regards
> AJ
>
>

"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:OdaSJI9DEHA.2932@tk2msftngp13.phx.gbl...
> The preferred method, to be quite honest, is to avoid "building a t-sql
> string" in the first place. See http://www.sommarskog.se/dynamic_sql.html
>
> The only problem I see with your chosen method is that 101 is not a safe
> format. I would rather use CHAR(8), 112 -- this producs YYYYMMDD, the
only
> truly safe format independent of language, dateformat and regional
settings.
> Also, if your DateTime column includes time information, you probably want
a
> range query (>= day and < day + 1) as opposed to a simple equality...
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
>
>
>
>
> "John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
> news:uXxEcC9DEHA.3404@TK2MSFTNGP10.phx.gbl...
> > when building a t-sql string , what is the preferred method to convert a
> > datetime parameter to a string ...
> >
> > i could imagine doing it like this : (MyDateTimeCol is a table column
of
> > type datetime)
> >
> > set @sql = @sql + 'MyDateTimeCol = ''' +
> > convert(varchar(10),@MyDateTimeParam,101) + ''''
> >
> > is there a better/preferred way ?
> >
> >
>
>

> second, what is so terrible about dynamic sql ?

> it's the only way good way
> i know of to write a stored procedure where parameters passed as nulls
> indicate that the current col value should be left alone.

"Erland Sommarskog" <sommar@algonet.se> wrote in message
news:Xns94B54F24A3AEYazorman@127.0.0.1...
> John A Grandy (johnagrandy-at-yahoo-dot-com) writes:
> > when building a t-sql string , what is the preferred method to convert a
> > datetime parameter to a string ...
> >
> > i could imagine doing it like this : (MyDateTimeCol is a table column
of
> > type datetime)
> >
> > set @sql = @sql + 'MyDateTimeCol = ''' +
> > convert(varchar(10),@MyDateTimeParam,101) + ''''
> >
> > is there a better/preferred way ?
>
> convert(char(8), @date, 112) is my favourite, as this is a safe format.
>
> However, best is to use sp_executesql, and pass the @date value as a
> parameter. Then you don't have to bother about converting at all. Look
> at http://www.sommarskog.se/dynamic_sql.html#sp_executesql for a
> quick example. For a longer example, look at
> http://www.sommarskog.se/dyn-search.html#sp_executesql.
>
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@algonet.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message news:uXxEcC9DEHA.3404@TK2MSFTNGP10.phx.gbl...
> when building a t-sql string , what is the preferred method to convert a
> datetime parameter to a string ...
>
> i could imagine doing it like this : (MyDateTimeCol is a table column of
> type datetime)
>
> set @sql = @sql + 'MyDateTimeCol = ''' +
> convert(varchar(10),@MyDateTimeParam,101) + ''''
>
> is there a better/preferred way ?
>

"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:uXxEcC9DEHA.3404@TK2MSFTNGP10.phx.gbl...
> when building a t-sql string , what is the preferred method to convert a
> datetime parameter to a string ...
>
> i could imagine doing it like this : (MyDateTimeCol is a table column of
> type datetime)
>
> set @sql = @sql + 'MyDateTimeCol = ''' +
> convert(varchar(10),@MyDateTimeParam,101) + ''''
>
> is there a better/preferred way ?
>
>

> when building a t-sql string , what is the preferred method to convert a
> datetime parameter to a string ...
>
> i could imagine doing it like this : (MyDateTimeCol is a table column of
> type datetime)
>
> set @sql = @sql + 'MyDateTimeCol = ''' +
> convert(varchar(10),@MyDateTimeParam,101) + ''''
>
> is there a better/preferred way ?

> except that then you risk incurring the wrath of others on this thread who
> feel that dynamic-sql is Ye Foule Beaste Of Ye Netherworlde ...