I am writing a SQL query to convert string to datetime:
SELECT CAST('2017-04-07.15-23-44' AS datetime)
When I am converting it to datetime getting an error
varchar data type to datetime data type resulted in out of range value.
-
5That is because it isn't properly formatted to be converted.dfundako– dfundako2021-04-23 15:54:30 +00:00Commented Apr 23, 2021 at 15:54
-
1Why are you using strings instead of dates in the first place? Fixing an invalid field or parameter type is far better than covering it up. Besides, I doubt any country on earth uses such a format for date and time. How could SQL Server guess what this string means?Panagiotis Kanavos– Panagiotis Kanavos2021-04-23 15:56:00 +00:00Commented Apr 23, 2021 at 15:56
-
@PanagiotisKanavos actually I am getting this from the filename.mahadev dhyani– mahadev dhyani2021-04-23 15:57:31 +00:00Commented Apr 23, 2021 at 15:57
-
What name is that? Again, that format isn't used by any country. How could SQL Server guess what it means? If you want to parse this custom format you'll have to write the code for it. It's a lot easier to parse such strings before inserting the data into the databasePanagiotis Kanavos– Panagiotis Kanavos2021-04-23 15:58:02 +00:00Commented Apr 23, 2021 at 15:58
-
2learn.microsoft.com/en-us/sql/t-sql/functions/… outlines the formats supported - and even then you have to specify the format to convert from.ADyson– ADyson2021-04-23 16:00:24 +00:00Commented Apr 23, 2021 at 16:00
|
Show 2 more comments
2 Answers
Split the string with the . as delimiter and in the 2nd part replace all '-' with ':'.
Concatenate the 2 parts again and then cast it to DATETIME:
DECLARE @d VARCHAR(20) = '2017-04-07.15-23-44';
SELECT CAST(LEFT(@d, CHARINDEX('.', @d) - 1) + ' ' +
REPLACE(SUBSTRING(@d, CHARINDEX('.', @d) + 1, LEN(@d)), '-', ':')
AS datetime
)
See the demo.
Comments
You can try the following to format as an ISO date
declare @date varchar(20)='2017-04-07.15-23-44'
select Cast(Replace(Replace(Stuff(Stuff(@date,14,1,':'),17,1,':'),'-',''),'.',' ') as datetime)
