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 a text field to a datetime datatype, who can figure this anyone??

Hello,
I'm trying to convert data on a text field to datetime datatype. This data
has invalid dates such as 10/30, 2/31235, 32/1/2004, 345/2345 etc.
I have been trying to convert whatever I can but I keep getting errors when
I perform a
SELECT CONVERT(datetime, text_field) FROM Old_data
Obviously my select statemt is abruptly terminated when I hit one of these
records with a invalid date.
I tried looping throught the records using a curson and try to capture and
check for errors:
SET @.user_1_date = convert(datetime, @.USER_1)
IF @.@.ERROR <> 0
BEGIN
PRINT 'ERROR FOUND'
END
ELSE
BEGIN
print 'NO ERROR FOUND'
END
I'm using PRINT for simplicity but the conversion error can not be
controlled through this method. SQL halts when it tries to convert the date.
I would love to be able to trap the error and skip the record and move on to
the new one. The bulk of the data is good, but there are over 175,000
records and I can not do this manually.
Any thoughts or suggestions?
ThanksAsked and answered by lw in the .programming group. Please don't
multi-post.
"Sd" <nowhere@.sadfs.com> wrote in message
news:3ac3890e730ead0e80076b10ff132aaa@.ne
ws.teranews.com...
> Hello,
> I'm trying to convert data on a text field to datetime datatype. This data
> has invalid dates such as 10/30, 2/31235, 32/1/2004, 345/2345 etc.
> I have been trying to convert whatever I can but I keep getting errors
when
> I perform a
> SELECT CONVERT(datetime, text_field) FROM Old_data
> Obviously my select statemt is abruptly terminated when I hit one of these
> records with a invalid date.
> I tried looping throught the records using a curson and try to capture and
> check for errors:
> SET @.user_1_date = convert(datetime, @.USER_1)
> IF @.@.ERROR <> 0
> BEGIN
> PRINT 'ERROR FOUND'
> END
> ELSE
> BEGIN
> print 'NO ERROR FOUND'
> END
> I'm using PRINT for simplicity but the conversion error can not be
> controlled through this method. SQL halts when it tries to convert the
date.
> I would love to be able to trap the error and skip the record and move on
to
> the new one. The bulk of the data is good, but there are over 175,000
> records and I can not do this manually.
> Any thoughts or suggestions?
> Thanks
>
>|||Sd (nowhere@.sadfs.com) writes:
> I have been trying to convert whatever I can but I keep getting errors
> when I perform a
> SELECT CONVERT(datetime, text_field) FROM Old_data
> Obviously my select statemt is abruptly terminated when I hit one of these
> records with a invalid date.
> I tried looping throught the records using a curson and try to capture and
> check for errors:
But since SQL Server thinks conversion error is reason enough for aborting
the batch, that does not fly.
But this should do:
SELECT CASE WHEN is_date(text_field) = 1
THEN convert(datetime, text_field)
END
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Error converting a text field to a datetime datatype, who can figure this anyone??

Hello,
I'm trying to convert data on a text field to datetime datatype. This data
has invalid dates such as 10/30, 2/31235, 32/1/2004, 345/2345 etc.
I have been trying to convert whatever I can but I keep getting errors when
I perform a
SELECT CONVERT(datetime, text_field) FROM Old_data
Obviously my select statemt is abruptly terminated when I hit one of these
records with a invalid date.
I tried looping throught the records using a curson and try to capture and
check for errors:
SET @.user_1_date = convert(datetime, @.USER_1)
IF @.@.ERROR <> 0
BEGIN
PRINT 'ERROR FOUND'
END
ELSE
BEGIN
print 'NO ERROR FOUND'
END
I'm using PRINT for simplicity but the conversion error can not be
controlled through this method. SQL halts when it tries to convert the date.
I would love to be able to trap the error and skip the record and move on to
the new one. The bulk of the data is good, but there are over 175,000
records and I can not do this manually.
Any thoughts or suggestions?
ThanksAsked and answered by lw in the .programming group. Please don't
multi-post.
"Sd" <nowhere@.sadfs.com> wrote in message
news:3ac3890e730ead0e80076b10ff132aaa@.news.teranews.com...
> Hello,
> I'm trying to convert data on a text field to datetime datatype. This data
> has invalid dates such as 10/30, 2/31235, 32/1/2004, 345/2345 etc.
> I have been trying to convert whatever I can but I keep getting errors
when
> I perform a
> SELECT CONVERT(datetime, text_field) FROM Old_data
> Obviously my select statemt is abruptly terminated when I hit one of these
> records with a invalid date.
> I tried looping throught the records using a curson and try to capture and
> check for errors:
> SET @.user_1_date = convert(datetime, @.USER_1)
> IF @.@.ERROR <> 0
> BEGIN
> PRINT 'ERROR FOUND'
> END
> ELSE
> BEGIN
> print 'NO ERROR FOUND'
> END
> I'm using PRINT for simplicity but the conversion error can not be
> controlled through this method. SQL halts when it tries to convert the
date.
> I would love to be able to trap the error and skip the record and move on
to
> the new one. The bulk of the data is good, but there are over 175,000
> records and I can not do this manually.
> Any thoughts or suggestions?
> Thanks
>
>|||Sd (nowhere@.sadfs.com) writes:
> I have been trying to convert whatever I can but I keep getting errors
> when I perform a
> SELECT CONVERT(datetime, text_field) FROM Old_data
> Obviously my select statemt is abruptly terminated when I hit one of these
> records with a invalid date.
> I tried looping throught the records using a curson and try to capture and
> check for errors:
But since SQL Server thinks conversion error is reason enough for aborting
the batch, that does not fly.
But this should do:
SELECT CASE WHEN is_date(text_field) = 1
THEN convert(datetime, text_field)
END
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

Error automating SQL DTS Pkg from VB6 to VB.Net

I am trying to convert a VB6 SQL 2000 created code into VB.NET. Everything
complies fine but then I get this error message:
An unhandled exception of type 'System.InvalidCastException' occurred in
Project1.exe
Additional information: QueryInterface for interface DTS.CustomTask failed.
This is the portion of the code that it is breaking on:
I found 2 ways to do this but both ways are giving me the same error message
Way #1:
Public Sub Task_Sub1(ByVal goPackage As DTS.Package2)
Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup
Dim oCustomTask1 As DTS.DataPumpTask2
oTask = CType(goPackage, DTS.Package2).Tasks.New("DTSDataPumpTask")
oTask = goPackage.Tasks.New("DTSDataPumpTask")
oCustomTask1 = oTask.CustomTask
Way #2:
Public Sub Task_Sub1(ByVal goPackage As Object)
Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup
Dim oCustomTask1 As DTS.DataPumpTask2
oTask = CType(goPackage, DTS.Package2).Tasks.New("DTSDataPumpTask")
oCustomTask1 = oTask.CustomTask
If anyone has an idea on what I need to change in the code I would greatly
appreciate it.
Thanks in advanceJohn,
Try with this excellent article from Gert Drapers:
http://www.sqldev.net/dts/DotNETCookBook.htm.
Dejan Sarka, SQL Server MVP
Mentor
www.SolidQualityLearning.com
"John" <John@.discussions.microsoft.com> wrote in message
news:B830B251-9289-4510-9ED8-6165AB2A0B92@.microsoft.com...
>I am trying to convert a VB6 SQL 2000 created code into VB.NET. Everything
> complies fine but then I get this error message:
> An unhandled exception of type 'System.InvalidCastException' occurred in
> Project1.exe
> Additional information: QueryInterface for interface DTS.CustomTask
> failed.
> This is the portion of the code that it is breaking on:
> I found 2 ways to do this but both ways are giving me the same error
> message
> Way #1:
> Public Sub Task_Sub1(ByVal goPackage As DTS.Package2)
> Dim oTask As DTS.Task
> Dim oLookup As DTS.Lookup
> Dim oCustomTask1 As DTS.DataPumpTask2
> oTask = CType(goPackage, DTS.Package2).Tasks.New("DTSDataPumpTask")
> oTask = goPackage.Tasks.New("DTSDataPumpTask")
> oCustomTask1 = oTask.CustomTask
> Way #2:
> Public Sub Task_Sub1(ByVal goPackage As Object)
> Dim oTask As DTS.Task
> Dim oLookup As DTS.Lookup
> Dim oCustomTask1 As DTS.DataPumpTask2
> oTask = CType(goPackage, DTS.Package2).Tasks.New("DTSDataPumpTask")
> oCustomTask1 = oTask.CustomTask
> If anyone has an idea on what I need to change in the code I would greatly
> appreciate it.
> Thanks in advance
>