Question about implicit string/datetime conversion.

(SQL Server 2000, SP3a)
Hello all!
If I have a query like:
select * from MyTable where MyDateField < '10/01/2003'
Does SQL Server implicitly convert that date string to a DATETIME datatype, or is it
implicitly converting all records' MyDateField to a string datatype? Since the VARCHAR(X)
is likely longer than the 8-byte datetime field, I suspect the latter, but how could I
find out for certain?
I suppose I could eliminate the ambiguity by explicitly converting:
select * from MyTable where MyDateField < convert(datetime, '10/01/2003')
Thanks for any help you can provide!
John PetersonJohn,
It follows the precedency rule...
http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_2js5.asp?frame=true
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:OHF$SjbkDHA.1084@.tk2msftngp13.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> If I have a query like:
> select * from MyTable where MyDateField < '10/01/2003'
> Does SQL Server implicitly convert that date string to a DATETIME
datatype, or is it
> implicitly converting all records' MyDateField to a string datatype?
Since the VARCHAR(X)
> is likely longer than the 8-byte datetime field, I suspect the latter, but
how could I
> find out for certain?
> I suppose I could eliminate the ambiguity by explicitly converting:
> select * from MyTable where MyDateField < convert(datetime, '10/01/2003')
> Thanks for any help you can provide!
> John Peterson
>|||Thanks, oj!
As I read this, that seems to suggest that the lower precedence string (varchar)
'10/01/2003' would be implicitly converted to the higher precedence datetime. In essence,
it would be equivalent to the second statement in my original post.
Thanks again for the information. I'll keep that link handy! :-)
"oj" <nospam_ojngo@.home.com> wrote in message
news:udw67nbkDHA.3700@.TK2MSFTNGP11.phx.gbl...
> John,
> It follows the precedency rule...
> http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_2js5.asp?frame=true
>
> --
> -oj
> RAC v2.2 & QALite!
> http://www.rac4sql.net
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:OHF$SjbkDHA.1084@.tk2msftngp13.phx.gbl...
> > (SQL Server 2000, SP3a)
> >
> > Hello all!
> >
> > If I have a query like:
> >
> > select * from MyTable where MyDateField < '10/01/2003'
> >
> > Does SQL Server implicitly convert that date string to a DATETIME
> datatype, or is it
> > implicitly converting all records' MyDateField to a string datatype?
> Since the VARCHAR(X)
> > is likely longer than the 8-byte datetime field, I suspect the latter, but
> how could I
> > find out for certain?
> >
> > I suppose I could eliminate the ambiguity by explicitly converting:
> >
> > select * from MyTable where MyDateField < convert(datetime, '10/01/2003')
> >
> > Thanks for any help you can provide!
> >
> > John Peterson
> >
> >
>|||Hi John!
Just be aware that SQL7 did it differently. SQL7 converted the "data side" to the "column side".
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"John Peterson" <j0hnp@.comcast.net> wrote in message news:udwiCubkDHA.1656@.tk2msftngp13.phx.gbl...
> Thanks, oj!
> As I read this, that seems to suggest that the lower precedence string (varchar)
> '10/01/2003' would be implicitly converted to the higher precedence datetime. In essence,
> it would be equivalent to the second statement in my original post.
> Thanks again for the information. I'll keep that link handy! :-)
>
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:udw67nbkDHA.3700@.TK2MSFTNGP11.phx.gbl...
> > John,
> >
> > It follows the precedency rule...
> >
> > http://msdn.microsoft.com/library/en-us/tsqlref/ts_da-db_2js5.asp?frame=true
> >
> >
> > --
> > -oj
> > RAC v2.2 & QALite!
> > http://www.rac4sql.net
> >
> >
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:OHF$SjbkDHA.1084@.tk2msftngp13.phx.gbl...
> > > (SQL Server 2000, SP3a)
> > >
> > > Hello all!
> > >
> > > If I have a query like:
> > >
> > > select * from MyTable where MyDateField < '10/01/2003'
> > >
> > > Does SQL Server implicitly convert that date string to a DATETIME
> > datatype, or is it
> > > implicitly converting all records' MyDateField to a string datatype?
> > Since the VARCHAR(X)
> > > is likely longer than the 8-byte datetime field, I suspect the latter, but
> > how could I
> > > find out for certain?
> > >
> > > I suppose I could eliminate the ambiguity by explicitly converting:
> > >
> > > select * from MyTable where MyDateField < convert(datetime, '10/01/2003')
> > >
> > > Thanks for any help you can provide!
> > >
> > > John Peterson
> > >
> > >
> >
> >
>