The conversion of a nvarchar data type to a datetime

Rabia Kurnaz 386

When I get a report via view, I get an error like this. how can i fix this Screenshot 2024-02-20 121825

Olaf Helper 44,501 Reputation points

2024-02-20T09:29:50.06+00:00

That's to less on information, please post some sample data and part of the view which converts the string to datetime.
Rabia Kurnaz 386 Reputation points

2024-02-20T09:33:54.9533333+00:00

@Olaf Helper Hi, The date format in the view proceeds like this: Format(o.deliverydate, 'dd.MM.yyyy'),
Yitzhak Khabinsky 25,856 Reputation points

2024-02-20T13:40:15.52+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;)

Accepted answer

Olaf Helper 44,501 Reputation points

2024-02-20T09:35:37.7133333+00:00
Date format like this 'dd.MM.yyyy'

German format, use the CONVERT function with format paramater 104 = German, see https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16

select convert(datetime, '20.02.2024', 104)
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

1 additional answer

LiHongMSFT-4306 27,016 Reputation points

2024-02-20T09:27:53.8433333+00:00

Hi @Rabia Kurnaz Have you checked if exists wrong date value like this: 2024-02-31.

Best regards,

Cosmog Hong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.
Rabia Kurnaz 386 Reputation points

2024-02-20T09:31:57.1+00:00

@LiHongMSFT-4306 Hi, Date format like this 'dd.MM.yyyy'

LiHongMSFT-4306 27,016 Reputation points

2024-02-20T09:44:25.8066667+00:00

I mean the date value that not actually exist, for example February 31th.

Olaf Helper 44,501 Reputation points

2024-02-20T10:35:32.7933333+00:00

I mean the date value that not actually exist, for example February 31th.

Then use the TRY_CONVERT function, it don't raise an error on faulty data: https://learn.microsoft.com/en-us/sql/t-sql/functions/try-convert-transact-sql?view=sql-server-ver16
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Your answer