Convert Varchar to Datetime in SQL Which Is Having Millisec

Convert varchar to datetime in sql which is having millisec

You can use style 121 but you can have only 3 digits for milliseconds (i.e yyyy-mm-dd hh:mi:ss.mmm(24h)) format.

declare @abc varchar(100)='2011-09-26 16:36:57.810' 
select convert(datetime,@abc,121)

So you can sort it out by limiting the varchar field to 23 characters before converting as:

declare @abc varchar(100)='2011-09-26 16:36:57.810000' 
select convert(datetime,convert(varchar(23),@abc),121)

Or use the Left() function to get first 23 characters as:

select convert(datetime,left(@abc,23),121)

Try to avoid storing date as string.

Convert varchar to datetime without millisecond

A datetime doesn't have that level of precision, instead use datetime2:

select cast('2020-10-10 07:30:00.0000000' as datetime2);

And to only show the desired string, cast it to a shorter string:

select cast(cast('2020-10-10 07:30:00.0000000' as datetime2) as varchar(19));

Obviously it makes no sense to cast a string to date and back again, but I assume you have simplified your actual use-case. Otherwise you could use:

select cast('2020-10-10 07:30:00.0000000' as varchar(19));

When converting string to datetime the milliseconds precision is changing

Datetime is only accurate to 3.33 milliseconds. If you try to wedge a value in that is more precise than that, MS SQL will round to the near acceptable value. More information is available here:

Milliseconds in my DateTime changes when stored in SQL Server

Convert 16-digit datetime string to datetime with microseconds

You can use the handy DATETIMEFROMPARTS:

DECLARE @s nvarchar(16) = '2022020804180454';

SELECT DATETIMEFROMPARTS(LEFT(@s,4), --year
                         SUBSTRING(@s,5,2), --month
                         SUBSTRING(@s,7,2), --day
                         SUBSTRING(@s,9,2), --hour
                         SUBSTRING(@s,11,2), --minute
                         SUBSTRING(@s,13,2), --second
                         SUBSTRING(@s,15,2)*10 --millisecond
                         )

Cast a varchar to minutes, seconds and milliseconds time format?

You must use STR_TO_DATE

SELECT STR_TO_DATE("1:38:096", "%i:%s:%f");

Result

STR_TO_DATE("1:38:096", "%i:%s:%f")
00:01:38.096000

This is a DATETIME, but you can extract minutes and use all other timefunction provided in the link

How to convert yyyymmddMilliseconds to datetime in SQL Server?

Interestingly, if you are using SQL Server 2005 and prior, you won't get the exact precision you want because DateTime values are rounded to increments of .000, .003 or .007. Thus, 51.234 becomes 51.233. However, if you use DateTime2, you can get the precision you want:

declare @x varchar(50)
set @x='20100218000051234'
Select DateAdd(ms, Cast(Substring(@x, 9, 9) As int), Cast(Substring(@x, 1, 8) As datetime))
Select DateAdd(ms, Cast(Substring(@x, 9, 9) As int), Cast(Substring(@x, 1, 8) As datetime2))

Results:
2010-02-18 00:00:51.233 
2010-02-18 00:00:51.2340000

Milliseconds wrong when converting from XML to SQL Server datetime

Yes, SQL Server rounds time to 3.(3) milliseconds:

SELECT CAST(CAST('2009-01-01 00:00:00.000' AS DATETIME) AS BINARY(8))
SELECT CAST(CAST('2009-01-01 00:00:01.000' AS DATETIME) AS BINARY(8))

0x00009B8400000000
0x00009B840000012C

As you can see, these DATETIME's differ by 1 second, and their binary representations differ by 0x12C, that is 300 in decimal.

This is because SQL Server stores the time part of the DATETIME as a number of 1/300 second ticks from the midnight.

If you want more precision, you need to store a TIME part as a separate value. Like, store time rounded to a second as a DATETIME, and milliseconds or whatever precision you need as an INTEGER in another columns.

This will let you use complex DATETIME arithmetics, like adding months or finding week days on DATETIME's, and you can just add or substract the milliseconds and concatenate the result as .XXXXXX+HH:MM to get valid XML representation.

---

Related Topics