Ошибка 241 sql

I keep getting the following error to my code when I either try to set it as cast() or convert(). Nothing seems to work.

Query:

SELECT TOP 100
    *
FROM [OptionsDW].[dbo].[Octagon5]
WHERE CONVERT(datetime2, CurrDate, 121) 
          BETWEEN CAST('2010-01-01' AS date) AND CAST('2010-06-31' AS date)

Result:

Msg 241, Level 16, State 1, Line 2 Conversion failed when converting
date and/or time from character string.

I know there are many similar queries but none of them helped with my problem.

I have an error in my stored procedure on SQL Server 2008.

I need to do stuff and insert data in my table only if the current element doesn’t already exist.

So I create a stored procedure:

ALTER PROCEDURE [dbo].[upload_bank_file] 
    @date varchar(10), 
    @date_valeur varchar(10), 
    @libelle varchar(255), 
    @mode varchar(10), 
    @code varchar(10), 
    @date_effet varchar(10), 
    @debit float, 
    @credit float
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

print @credit;
IF Not EXISTS (SELECT 1 FROM dbo.bank_import WHERE dbo.bank_import.Libelle = @libelle)
    --DROP PROCEDURE upload_bank_file; 
Begin 
    IF (SUBSTRING(@date_effet, 4, 2) LIKE '01' AND SUBSTRING(@date, 1, 2) LIKE '12')
        SET @date_effet = CONVERT(varchar, (CONVERT(int, SUBSTRING(@date, 7, 2)) + 1)) + '-' + @date_effet;
    ELSE
        SET @date_effet = SUBSTRING(@date, 7, 2) + '-' + @date_effet;

    insert into dbo.bank_import ([Date], [Date_Valeur], [Libelle], [Mode], [Code], [Date_Effet], [Debit], [Credit])
    VALUES (CONVERT(datetime, @date, 120), CONVERT(datetime, @date_valeur, 120), @libelle, @mode, @code, CONVERT(datetime, @date_effet, 120), @debit, @credit);
    END
END

When I test it via my php moulinette (to automatize file parsing and call to the stored procedure), I got the following error :

[0] => 22007
[SQLSTATE] => 22007
[1] => 241
[code] => 241
[2] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Échec de la conversion de la date et/ou de l’heure à partir d’une chaîne de caractères.
[message] => [Microsoft][SQL Server Native Client 11.0][SQL Server]Échec de la conversion de la date et/ou de l’heure à partir d’une chaîne de caractères.

I’m passing date to the stored procedure via varchar, in yyyy/mm/dd format, and mm/dd format for @date parameter.

The SQL Server Management Studio, when I do

EXEC upload_bank_file 'with', 'fake', 'parameters' ...

is locating the error on the line 20 of my stored procedure, which is the line where I do my «If Exists ( Select … )», only when my libelle didn’t exists in the table.

I googled this error several times, but i have found nothing useful. I passed my date on yyyy/mm/dd format and I have specify the SET DATEFORMAT ymd flag to my stored procedure, but my problem remains.

If someone have any idea to help me, I will be grateful!

(English is not my mother tongue, please excuse me for the errors …)

Thanks !

Server: Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time
from character string.

This error usually occurs when a date and time value is being converted from a string representation to a DATETIME data type representation and SQL Server cannot perform the conversion due to an incorrectly formatted date/time value.

Here are a few samples of how the error can be encountered:

SELECT CAST('2011 01 01' AS DATETIME) -- Missing Date Separators
    
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

SELECT CAST('2011 Febraury 01' AS DATETIME) -- Misspelled Month

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

SELECT CAST('01 01 2011' AS DATETIME) -- Missing Date Separators

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

SELECT CAST('25 de Diciembre 2011' AS DATETIME) -- Date in Different Language
    
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

SELECT CAST('01//2011' AS DATETIME) -- Missing Day Part
    
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

There are other different ways of how this error can be encountered but what’s common among all of them is that the string value cannot be converted by SQL Server into a DATETIME data type.

A different error message will be generated if the date/time string value being converted to DATETIME is before January 1, 1753 or if either the month or day is not within the allowable values, as can be seen in the following script:

SELECT CAST('01/01/1700' AS DATETIME) -- Before January 1, 1753

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

SELECT CAST('02/29/2011' AS DATETIME) -- Year 2011 is not a leap year

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

SELECT CAST('06/31/2011' AS DATETIME) -- June 31 is not a valid date

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

Solution / Work Around:

To avoid this error, make sure that the character value containing the date/time value is a valid date/time that can be converted to DATETIME data type.

If the data is coming from another table where the column containing the date value is defined as VARCHAR and the data will be loaded to a table where the destination column is defined as DATETIME, the ISDATE() function can be used to determine whether an input expression is a valid date. The ISDATE() function returns 1 if the input expressions is a valid date; otherwise it returns 0.

Here’s an example of how to load data coming from one table into another where the column is defined as VARCHAR in the source table and defined as DATETIME in the destination table:

CREATE TABLE [dbo].[Transaction] (
    [TransactionName]       VARCHAR(50),
    [TransactionDate]       DATETIME,
    [Amount]                MONEY
)

CREATE TABLE [dbo].[tmpTransaction] (
    [TransactionName]       VARCHAR(50),
    [TransactionDate]       VARCHAR(20),
    [Amount]                MONEY
)

INSERT INTO [dbo].[tmpTransaction] ( [TransactionName], [TransactionDate], [Amount] )
VALUES ( 'ATM Deposit', '02/19/2011', 1000.00 )

INSERT INTO [dbo].[tmpTransaction] ( [TransactionName], [TransactionDate], [Amount] )
VALUES ( 'ATM Withdrawal', '02//2011', 250.00 )

INSERT INTO [dbo].[tmpTransaction] ( [TransactionName], [TransactionDate], [Amount] )
VALUES ( 'Cash Withdrawal', '01/01/2011', 5000.00 )

INSERT INTO [dbo].[Transaction] ( [TransactionName], [TransactionDate], [Amount] )
SELECT [TransactionName], [TransactionDate], [Amount]
FROM [dbo].[tmpTransaction]
WHERE ISDATE([TransactionDate]) = 1

(2 row(s) affected)

Alternatively, if all data needs to be loaded to the destination table, the invalid date values can be converted to NULL using the following:

INSERT INTO [dbo].[Transaction] ( [TransactionName], [TransactionDate], [Amount] )
SELECT [TransactionName], CASE WHEN ISDATE([TransactionDate]) = 1
                               THEN [TransactionDate] ELSE NULL END, [Amount]
FROM [dbo].[tmpTransaction]

(3 row(s) affected)

If the data is coming from an external file, such as a text file, and the file is being imported to a table where one of the columns has a data type of DATETIME and this error is encountered, one way to work around this is to load the data from the external file into a temporary table first where the date column will be defined as VARCHAR then follow the steps provided above in loading the data from this temporary table into the destination table containing the DATETIME column.