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
> > >
> > >
> >
> >
>

Question about by Import oracle data and field is null

Hi, I've a question about importing Oracle data and some fields are null. I get an error 'Conversion failed because the data value overflowed the specified type'. When i look in preview query result, via OLE db Source editor > Preview, this field contains '<value too big to display>'.

What do i do wrong? Can somebody help me?

Thanks in advance

Olaf

It may be helpful to have a few more details. Which version of SQL Server and Oracle are you using? And which method and tool are you using to import data?|||

Hello Buck,

I work with SQL server 2005 and Oracle 7.

Olaf

|||Olaf - SQL Server has several methods to export and import data, as does Oracle. For most data cleansing issues, the simplest way to eliminate data format differences is to export the data from Oracle to a text file and then import that to SQL Server. If you're looking for something that is simpler, such as using SQL Server Integration Services (SSIS) then you'll need to check the data types on both systems to ensure that they match up. You can also "push" the data from Oracle using their export tools. Whether you pull the data from Oracle using SSIS or push it from Oracle, both servers need to have the latest client drivers for each other.|||Hi Buck,
What is the problem. I have a made a SSIS, in VS2005, with an ole db source, settings to the oracle db, and an ole db destination, settings to sql 2005 db. When i look in preview

query result, via OLE db Source editor > Preview, afield

contains '<value too big to display>'. This field is a datetime oracle field and it's empty. How do i check if it's empty? And what do this field then get for an value so that i don't get problem with importing it in to SQL?

Thanks in advance

Olaf
|||

As I mentioned, you'll need the latest drivers on your SQL Server System for Oracle. Also, you can check the data you're about to import using the OPENQUERY statement in SQL Server. Look in Books Online for more information on how to use that statement.