Error converting Varchar to Int - SQL 2000

Hello, I'm having problems with this piece of Transact SQL. What I
need to do is to get a date 2 months ahead of an entered date.
However, I am not supposed to use this: @.dt+60 (in order to get 60 days
exactly). Consequently, if I've entered '12/21/05' as the original
date, I need to get the 2 month date as '2/21/06' regardless if they're
60 days or not. As you can see, the only thing that the new date will
be changed is going to be the month. Thus, the dates would convert as
follows: '5/5/05' to '7/5/05', '2/21/05' to '4/21/05' and so on except
for the months 11 and 12, which in that case would be 1 and 2 of the
following year.
This is what I have so far.
DECLARE @.dt DATETIME
DECLARE @.2dt VARCHAR
SET @.dt = '12/14/06'
SET @.2dt =
CASE
WHEN DATEPART(mm, @.dt) =11 THEN '11/' & DATEPART(dd,@.dt) + '/' &
DATEPART(yy,@.dt)+1
WHEN DATEPART(mm, @.dt) =12 THEN '12/' & DATEPART(dd,@.dt) + '/' &
DATEPART(yy,@.dt)+1
ELSE DATEPART(mm, @.dt) +2 & '/' & DATEPART(dd,@.dt) + '/' &
DATEPART(yy,@.dt)
END
print @.2dt
When I test it in the query analyzer, I get the this error: Syntax
error converting the varchar value '12/' to a column of data type int.
I tried to use the CAST and CONVERT function, it did not work. Perhaps
I was doing it wrong.
Any help would be appreciated.Doesn't this work for you?
DECLARE @.dt DATETIME
DECLARE @.2dt datetime
SET @.dt = '12/14/06'
SET @.2dt = dateadd(m,2,@.dt)
print convert(varchar,@.2dt)
http://sqlservercode.blogspot.com/|||Gosh! Thank you! This is exactly what I was looking for! You're a
genius!
I had this code as part of an Access query that I needed to convert to
SQL 2000 and it was driving me crazy. I'm so glad SQL has a function
that does this.
Thanks again!
JR
SQL wrote:
> Doesn't this work for you?
> DECLARE @.dt DATETIME
> DECLARE @.2dt datetime
> SET @.dt = '12/14/06'
> SET @.2dt = dateadd(m,2,@.dt)
> print convert(varchar,@.2dt)
>
> http://sqlservercode.blogspot.com/|||note: the reason for the error is that the '&' operator in sql is the
bitwise AND operator - not a string concatenation operator
so it was trying to convert '12/' to an int before performing the
bitwise AND.
ILCSP@.NETZERO.NET wrote:
> Hello, I'm having problems with this piece of Transact SQL. What I
> need to do is to get a date 2 months ahead of an entered date.
> However, I am not supposed to use this: @.dt+60 (in order to get 60 days
> exactly). Consequently, if I've entered '12/21/05' as the original
> date, I need to get the 2 month date as '2/21/06' regardless if they're
> 60 days or not. As you can see, the only thing that the new date will
> be changed is going to be the month. Thus, the dates would convert as
> follows: '5/5/05' to '7/5/05', '2/21/05' to '4/21/05' and so on except
> for the months 11 and 12, which in that case would be 1 and 2 of the
> following year.
> This is what I have so far.
> DECLARE @.dt DATETIME
> DECLARE @.2dt VARCHAR
> SET @.dt = '12/14/06'
> SET @.2dt =
> CASE
> WHEN DATEPART(mm, @.dt) =11 THEN '11/' & DATEPART(dd,@.dt) + '/' &
> DATEPART(yy,@.dt)+1
> WHEN DATEPART(mm, @.dt) =12 THEN '12/' & DATEPART(dd,@.dt) + '/' &
> DATEPART(yy,@.dt)+1
> ELSE DATEPART(mm, @.dt) +2 & '/' & DATEPART(dd,@.dt) + '/' &
> DATEPART(yy,@.dt)
> END
>
> print @.2dt
>
> When I test it in the query analyzer, I get the this error: Syntax
> error converting the varchar value '12/' to a column of data type int.
> I tried to use the CAST and CONVERT function, it did not work. Perhaps
> I was doing it wrong.
> Any help would be appreciated.
>

error converting date time

Hi i m tring to convert a date time

declare @.a datetime
declare @.b varchar(10)
set @.b='26/04/2004'
set @.a= Convert(datetime, @.b)

but it gives me this error:

Server: Msg 242, Level 16, State 3, Line 5
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

I believe is my date format..i want to know how to make sure that i am the dd/mm/yyyy format is correct way?Why are you sending the date in as a varchar ?|||Use SET DATEFORMAT

Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data.|||Hi,
thanks for all ur reply.. basically i wanted to send date in as dateTime but i cant because i cant assign a date to null so i have to send it to my database and from there i convert it to valid date time.

Thanks alot.

Error converting data type DBTYPE_DBTIMESTAMP to datetime

I'm getting the error Error converting data type DBTYPE_DBTIMESTAMP to datetime when I try to import a date field from Oracle to SQL Server.

SELECT CAST(cancel_dt as datetime) FROM OPENQUERY(orcldb, 'SELECT cancel_dt FROM tablename WHERE id= 12345')

The date in Oracle is 19-JUN-99, but it's coming over as 06/19/0999 and it throws the error because the year 999 is invalid in SQL Server. Any ideas on why Oracle or SQL server would convert the year to 0999 instead of 1999?
This is also happening on a value 24-SEP-07 coming over as 09/24/0207

Most other date values work, so I'd really like to get in depth into the process that occurs when converting to a SQL Server datetime.Why not use the conversion function in Oracle to produce a 4 digit year?|||I found the problem to be that SQL Server only accepts dates from January 1, 1753 through December 31, 9999 whereas Oracle allows dates January 1, 4712 BC to December 31, 9999 AD.

So these dates that are coming over with years of 999 or 207 produce an error because they are invaild in SQL Server.|||That's correct...

M$ figured that because the calendar was adjusted in that century..(they lost several days) that the date function would produce invalid results...think DATEDIFF, DATEADD, ect

lunacy

anyway...your dates are not 0999

Oracle has the ability to produce the dates you need. The date formatting function in Oracle are far superior to SQL Server...

Just return that date as a varchar in your select, then convert it...|||So what is the actual 4-digit year representation for 19-JUN-99? Is it 06/19/0999 or 06/19/1999?
And what about 24-SEP-07? 09/24/0207 or 09/24/2007?

If the latter is the answer to both then it's not SQL Server, it's how you bring the data in and how much guessing you want SQL Server to do. You also need to check "two digit year cutoff option".|||MY problem with the dates is that 19-JUN-99 is supposed to be 1999, but somehow this date was loaded as June 19, 999 instead. So it is coming over correctly. There's just some data cleaning to be done.

This is what happens when you use someone elses data :mad:|||These 2 calls produce the right result:

select cast('24-SEP-07' as datetime) --09/24/2007
select cast('19-JUN-99' as datetime) --06/19/1999|||Check how you set up the linked server to Horacle. See if there is any datetime/currency setting that can affect this.

Error converting data type DBTYPE_DBTIMESTAMP to datetime

Hi

I am trying to transfer data from Access 2000 database to SQL server 2005 via SSIS. My access database table has a field with data type Date/Time and SQL server table has a field with data type datetime. When I am running the SSIS package, it is throwing the following error and stops execution.

[SQL Server Destination [12466]] Error: An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E07 Description: "Error converting data type DBTYPE_DBTIMESTAMP to datetime.".

Please help.

Thanks in advance,
Ramzee

According to BOL (http://msdn2.microsoft.com/en-us/library/ms175970.aspx) the types can be converted, so that should work. Have you identified the values that fail? Using the error output is a very easy way to capture this data. My best guess for now is the values are out of range for the SQL datetime type. A derived column transformation could be used to massage these values depending on requirements.|||

Thanks Darren!

I used the script component to identify the wrong date entries. Now it works!

Thanks once again.

|||

I am having the same error. A simple select from Query Analyzer

Select * from HCPROD..HC.UNIT U

Even if I specifically reference a number column I get the same error

Select BLOCK_ID from HCPROD..HC.UNIT U

The way I see it is that there is a column, probably the DATE_MODIFIED column, somewhere in the database that has a date & time value that the driver cannot process. Why the error occurs even if I do not reference the column is a mystery.

I am using Microsoft OLE DB Provider for Oracle

Note that I have insured that every date field in the table is in 20th or 21st century

|||Some more information. We are using Oracle version 10g. I wonder if there is an updated driver from Microsoft? We searched and could not find one ourselves.|||SELECT TOP 10 * FROM [ORACLE_LOOKUP]..[SchemaName].[TableName] AlaisName

Error converting data type DBTYPE_DBTIMESTAMP to datetime

SELECT * FROM
OPENQUERY (
ORACLE_LOOKUP,
'SELECT TO_CHAR(DateColumnName) FROM TableName')

The following workaround was foud on the internet

A workaround would be to use the OPENQUERY function in SQL Server. This
function allows you to execute a pass-through query on the linked server.
By issuing a pass-through query, you can then take advantage of Oracle's
built-in functions to convert the date to a character data type or NULL.
Examples of both are as follows:

In the following example, the column "hiredate" is converted to a string in
the format of MM-DD-YYYY.

SELECT *
FROM OPENQUERY(, 'SELECT empno,
TO_CHAR(hiredate,''MM-DD-YYYY'') FROM scott.emp')

In this example we use a combination of two Oracle built-in functions,
DECODE and GREATEST to convert any hiredate that is earlier than 01/01/1753
(SQL Server's lower bound) to NULL.

SELECT *
FROM OPENQUERY(,'SELECT DECODE (hiredate,
GREATEST(hiredate, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), hiredate, NULL)
FROM scott.emp')

In case you are not familiar with Oracle built-ins, here is a description of
the two used in the query above.

DECODE is similar to a case statement in SQL Server. Its syntax is as
follows :

DECODE(, , , , ,...,
)

The is compared to each value. If there is a match,
the corresponding is returned. Otherwise, the value is
returned.

GREATEST returns the greatest value in the list of expressions.

GREATEST(, , ...)
Reply With Quote

Error converting data type DBTYPE_DBTIMESTAMP to datetime

Hi

I am trying to transfer data from Access 2000 database to SQL server 2005 via SSIS. My access database table has a field with data type Date/Time and SQL server table has a field with data type datetime. When I am running the SSIS package, it is throwing the following error and stops execution.

[SQL Server Destination [12466]] Error: An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E07 Description: "Error converting data type DBTYPE_DBTIMESTAMP to datetime.".

Please help.

Thanks in advance,
Ramzee

According to BOL (http://msdn2.microsoft.com/en-us/library/ms175970.aspx) the types can be converted, so that should work. Have you identified the values that fail? Using the error output is a very easy way to capture this data. My best guess for now is the values are out of range for the SQL datetime type. A derived column transformation could be used to massage these values depending on requirements.|||

Thanks Darren!

I used the script component to identify the wrong date entries. Now it works!

Thanks once again.

|||

I am having the same error. A simple select from Query Analyzer

Select * from HCPROD..HC.UNIT U

Even if I specifically reference a number column I get the same error

Select BLOCK_ID from HCPROD..HC.UNIT U

The way I see it is that there is a column, probably the DATE_MODIFIED column, somewhere in the database that has a date & time value that the driver cannot process. Why the error occurs even if I do not reference the column is a mystery.

I am using Microsoft OLE DB Provider for Oracle

Note that I have insured that every date field in the table is in 20th or 21st century

|||Some more information. We are using Oracle version 10g. I wonder if there is an updated driver from Microsoft? We searched and could not find one ourselves.|||SELECT TOP 10 * FROM [ORACLE_LOOKUP]..[SchemaName].[TableName] AlaisName

Error converting data type DBTYPE_DBTIMESTAMP to datetime

SELECT * FROM
OPENQUERY (
ORACLE_LOOKUP,
'SELECT TO_CHAR(DateColumnName) FROM TableName')

The following workaround was foud on the internet

A workaround would be to use the OPENQUERY function in SQL Server. This
function allows you to execute a pass-through query on the linked server.
By issuing a pass-through query, you can then take advantage of Oracle's
built-in functions to convert the date to a character data type or NULL.
Examples of both are as follows:

In the following example, the column "hiredate" is converted to a string in
the format of MM-DD-YYYY.

SELECT *
FROM OPENQUERY(, 'SELECT empno,
TO_CHAR(hiredate,''MM-DD-YYYY'') FROM scott.emp')

In this example we use a combination of two Oracle built-in functions,
DECODE and GREATEST to convert any hiredate that is earlier than 01/01/1753
(SQL Server's lower bound) to NULL.

SELECT *
FROM OPENQUERY(,'SELECT DECODE (hiredate,
GREATEST(hiredate, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), hiredate, NULL)
FROM scott.emp')

In case you are not familiar with Oracle built-ins, here is a description of
the two used in the query above.

DECODE is similar to a case statement in SQL Server. Its syntax is as
follows :

DECODE(, , , , ,...,
)

The is compared to each value. If there is a match,
the corresponding is returned. Otherwise, the value is
returned.

GREATEST returns the greatest value in the list of expressions.

GREATEST(, , ...)
Reply With Quote

Error converting data type DBTYPE_DBTIMESTAMP to datetime

Hi

I am trying to transfer data from Access 2000 database to SQL server 2005 via SSIS. My access database table has a field with data type Date/Time and SQL server table has a field with data type datetime. When I am running the SSIS package, it is throwing the following error and stops execution.

[SQL Server Destination [12466]] Error: An OLE DB error has occurred. Error code: 0x80040E07. An OLE DB record is available. Source: "Microsoft OLE DB Provider for SQL Server" Hresult: 0x80040E07 Description: "Error converting data type DBTYPE_DBTIMESTAMP to datetime.".

Please help.

Thanks in advance,
Ramzee

According to BOL (http://msdn2.microsoft.com/en-us/library/ms175970.aspx) the types can be converted, so that should work. Have you identified the values that fail? Using the error output is a very easy way to capture this data. My best guess for now is the values are out of range for the SQL datetime type. A derived column transformation could be used to massage these values depending on requirements.|||

Thanks Darren!

I used the script component to identify the wrong date entries. Now it works!

Thanks once again.

|||

I am having the same error. A simple select from Query Analyzer

Select * from HCPROD..HC.UNIT U

Even if I specifically reference a number column I get the same error

Select BLOCK_ID from HCPROD..HC.UNIT U

The way I see it is that there is a column, probably the DATE_MODIFIED column, somewhere in the database that has a date & time value that the driver cannot process. Why the error occurs even if I do not reference the column is a mystery.

I am using Microsoft OLE DB Provider for Oracle

Note that I have insured that every date field in the table is in 20th or 21st century

|||Some more information. We are using Oracle version 10g. I wonder if there is an updated driver from Microsoft? We searched and could not find one ourselves.|||SELECT TOP 10 * FROM [ORACLE_LOOKUP]..[SchemaName].[TableName] AlaisName

Error converting data type DBTYPE_DBTIMESTAMP to datetime

SELECT * FROM
OPENQUERY (
ORACLE_LOOKUP,
'SELECT TO_CHAR(DateColumnName) FROM TableName')

The following workaround was foud on the internet

A workaround would be to use the OPENQUERY function in SQL Server. This
function allows you to execute a pass-through query on the linked server.
By issuing a pass-through query, you can then take advantage of Oracle's
built-in functions to convert the date to a character data type or NULL.
Examples of both are as follows:

In the following example, the column "hiredate" is converted to a string in
the format of MM-DD-YYYY.

SELECT *
FROM OPENQUERY(, 'SELECT empno,
TO_CHAR(hiredate,''MM-DD-YYYY'') FROM scott.emp')

In this example we use a combination of two Oracle built-in functions,
DECODE and GREATEST to convert any hiredate that is earlier than 01/01/1753
(SQL Server's lower bound) to NULL.

SELECT *
FROM OPENQUERY(,'SELECT DECODE (hiredate,
GREATEST(hiredate, TO_DATE(''01/01/1753'',''MM/DD/YYYY'')), hiredate, NULL)
FROM scott.emp')

In case you are not familiar with Oracle built-ins, here is a description of
the two used in the query above.

DECODE is similar to a case statement in SQL Server. Its syntax is as
follows :

DECODE(, , , , ,...,
)

The is compared to each value. If there is a match,
the corresponding is returned. Otherwise, the value is
returned.

GREATEST returns the greatest value in the list of expressions.

GREATEST(, , ...)
Reply With Quote