How to convert yyyymmddMilliseconds to datetime in SQL Server?

Question

I got a table with some time data in the format of yyyymmddMilliseconds. For example, 20100218000051234. How to convert this into DateTime type? In SQL Server 2008.

Answer 1

You'll need to use a combination of convert and substring. There's a pretty good example you can extrapolate from at http://www.sqlusa.com/bestpractices/datetimeconversion/.

You can also find documentation on the T-SQL string manipulation functions here.

Answer 2

try this:

declare @x varchar(50)
set @x='20100218000051234'
select DATEADD(ms,CONVERT(int,RIGHT(@x,9)),CONVERT(datetime,LEFT(@x,8)))

output

-----------------------
2010-02-18 00:00:51.233

(1 row(s) affected)

Answer 3

In your example above, it is not clear whether 20100218000051234 represents

Year: 2010
Month: 02 (February
Day: 18
Milliseconds: 000051234

Or:

Year: 2010
Month: 02 (February
Day: 18
Hours: 00
Minutes: 00
Seconds: 51
Milliseconds: 234

Answer 4

You will need to use SUBSTRING and CAST.

SELECT CAST(
       SUBSTRING(dateString, 1, 4 ) + '-' +
       SUBSTRING(dateString, 5, 2) + '-' +
       SUBSTRING(dateString, 7, 2)
       AS date )

You'll need to play with the values and add info for the time, but the finished product should look like this:

SELECT CAST('1998-02-23 14:23:05' AS date)

Answer 5

As an alternative (useful if you also have multiple other "information parsing" to do, you may find that this is an example of where you might want to use a SQLCLR function

using System;
using System.Data.SqlTypes;
using System.Globalization;

public partial class UserDefinedFunctions
{
   [Microsoft.SqlServer.Server.SqlFunction]
   public static SqlDateTime Parse(SqlString s)
   {
      if (s.IsNull)
         return SqlDateTime.Null;

      return new SqlDateTime(DateTime.ParseExact(s.Value,"yyyyMMddhhmmssfff",CultureInfo.InvariantCulture));
   }
};

Answer 6

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