sql-serverdatetimevarcharoutofrangeexception

Order by results are out-of-range when converting varchar to datetime with missing time

I have a varchar row in a log-db that normally looks like 17.09.2020 00:00:01 and I can CAST() it to datetime and order by it without problems, e.g. this simplified query:

SELECT CAST(my_date_and_time AS datetime)
FROM my_table
ORDER BY CAST(my_date_and_time AS datetime) DESC

Now the problem is, that tonight I've got a log-entry at exactly 00:00:00 and the logging-script or database cut off the time from the varchar field and so now it only contains 17.09.2020 instead of 17.09.2020 00:00:00. When I try to cast this one to datetime it results in an out-of-range error, probably because the time part is missing?

Unfortunately I have no access to the logging script to change it there. So is there any way to convert this to an useable format within the query?

EDIT: Here's an example of what the my_date_and_time row looks like:

|---------------------|
|   my_date_and_time  |
|---------------------|
| 17.09.2020 05:29:53 |
|---------------------|
| 17.09.2020 00:02:11 |
|---------------------|
|      17.09.2020     |  
|---------------------|
| 16.09.2020 23:59:38 |
|---------------------|
| 16.09.2020 23:59:18 |
|---------------------|

EDIT2: By further testing I was able to narrow the error further down. It is NOT because of the timestamp as initially assumed, which has no time information. The problem actually affects records that are not compliant.

These data records are missing the preceding 0 for the month and one point is too much after the year. e.g. 16.9.2020. 14:22:23

I'm on the verge of ripping off the head of whoever programmed this ..

Is there any conceivable way to get the correct values ​​back via query anyway?

Sorry for the misunderstandings and thanks to everyone who has contributed to solving the problem so far

Solution

  • As I mention in the comment, really you should be fixing your data type.

    If all your dates are in the format dd.MM.yyyy hh:mm:ss then you use use CONVERT and a style code to convert them regardless of settings:

    SELECT TRY_CONVERT(datetime,Your_varchar,103)
FROM dbo.YourTable;

    If any return NULL they either have the value NULL, or failed to convert.

    But back to fixing your data.

    Firstly, let's do this in a new column:

    ALTER TABLE dbo.YourTable ADD Actual_date_and_Time datetime NULL

    Now we can UPDATE that value by coverting the value:

    UPDATE dbo.YourTable
SET Actual_date_and_Time = TRY_CONVERT(datetime,Varchar_Date_and_time,103);

    Then you can query an actual datetime column.

    You can then review your new data and see if any if bad, and amend:

    SELECT Varchar_Date_and_time
FROM dbo.YourTable
WHERE Varchar_Date_and_time IS NOT NULL
  AND Actual_date_and_Time;

    Then, finally, you can DROP the old column and rename the new one (if you want to):

    ALTER TABLE dbo.YourTable DROP COLUMN Varchar_Date_and_time;
EXEC sys.sp_rename N'dbo.YourTable.Actual_date_and_Time',N'Varchar_Date_and_time','COLUMN';