Error when conversion of a varchar data type to a datetime

Question

Error when conversion of a varchar data type to a datetime

Ramana Kopparapu 306

Hi,

I have column NMDT decimal(8,0) - In one table

Last_Date datetime2(7) - In another table

wants to convert NMDT (decimal) column as Last_Date (Datetime2). I write below query but getting error message.

CASE WHEN NMDT = 0 THEN NULL ELSE CONVERT(DATETIME, CAST(NMDT AS VARCHAR(10)) + STUFF(STUFF(STUFF(right('000000'+ CAST(NMDT AS VARCHAR(10)), 6), 1, 0, ' '), 4, 0, ':'), 7, 0, ':')) END AS Last_Date,

Error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Please assist me.

Thanks in advance.

Viorel 122.5K Reputation points

2023-12-07T16:07:00.24+00:00

Show a typical a example of NMDT and the expected Last_Date.
Yitzhak Khabinsky 26,586 Reputation points

2023-12-07T16:46:21.3733333+00:00

While asking a question you need to provide a minimal reproducible example:

(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.

(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.

(3) Desired output based on the sample data in the #1 above.

(4) Your SQL Server version (SELECT @@version;)
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-12-07T22:01:04.98+00:00

You asked a similar question the other day, https://learn.microsoft.com/en-us/answers/questions/1458030/decimal-to-date-in-sql, and Dan Guzman gave you a simple and concise answer. Why are you not using his solution, instead of the above, which looks horrendously complicated.

By the way, have a look at datetime2fromparts.
Anonymous

2023-12-13T02:30:13.88+00:00

Hi,

You can take a look at the answers below. If it can help you, please accept it.

1 answer

Your answer

Viorel 122.5K Reputation points

2023-12-07T16:07:00.24+00:00

Show a typical a example of NMDT and the expected Last_Date.
Yitzhak Khabinsky 26,586 Reputation points

2023-12-07T16:46:21.3733333+00:00

While asking a question you need to provide a minimal reproducible example:

(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.

(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.

(3) Desired output based on the sample data in the #1 above.

(4) Your SQL Server version (SELECT @@version;)
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-12-07T22:01:04.98+00:00

You asked a similar question the other day, https://learn.microsoft.com/en-us/answers/questions/1458030/decimal-to-date-in-sql, and Dan Guzman gave you a simple and concise answer. Why are you not using his solution, instead of the above, which looks horrendously complicated.

By the way, have a look at datetime2fromparts.
Anonymous

2023-12-13T02:30:13.88+00:00

Hi,

You can take a look at the answers below. If it can help you, please accept it.

Answer 1

Olaf Helper 47,436

varchar data type to a datetime data type resulted in an out-of-range value.

Datetime has a range between year 1753 and 9999; you must have date values below 1753.

Use the data type date or datetime2, see

https://learn.microsoft.com/en-us/sql/t-sql/data-types/data-types-transact-sql?view=sql-server-ver16

Share via

Error when conversion of a varchar data type to a datetime

1 answer

Your answer