|
|
SQL Server Error Messages - Msg 241
Error Message
Server: Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time
from character string.
Causes
The DATETIME data type is used for representing the date and the time of day between January 1, 1753 through December 31, 9999, with an accuracy rounded to increments of .000, .003 or .007 seconds. DATETIME values are stored as two 4-byte integers. The first 4 bytes store the number of days before or after the base date of January 1, 1900 while the second 4 bytes store the time of day represented as the number of 1/300-second units after midnight.
|
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('2025 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('2025 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 2025' 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 2024' 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//2025' 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/2025' AS DATETIME) -- Year 2025 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/2025' 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/2025', 1000.00 )
INSERT INTO [dbo].[tmpTransaction] ( [TransactionName], [TransactionDate], [Amount] )
VALUES ( 'ATM Withdrawal', '02//2025', 250.00 )
INSERT INTO [dbo].[tmpTransaction] ( [TransactionName], [TransactionDate], [Amount] )
VALUES ( 'Cash Withdrawal', '01/01/2025', 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.
|