MS SQL Server String to Datetime

Question

I have below string of date time

2017-09-06 00:36:32.473491+05:30

I want to store this date time into MS SQL Server table and my datatype for this column is datetime.

I tried CAST but it gives me the following error Conversion failed when converting date and/or time from character string. So please help me to fix this. I am using MS SQL Server 2014

How do you import it? Maybe you should parse it there to DateTime. In C# f.e. it's easy: DateTime.Parse("2017-09-06 00:36:32.473491+05:30") works as it is. — Tim Schmelter
– Tim Schmelter, Commented Oct 24, 2017 at 13:49
@TimSchmelter I am not using any programming language. I try select CAST('2017-09-06 00:36:32.473491+05:30' AS DateTime) as date_time; in MS SQL Server itself. — user3441151
– user3441151, Commented Oct 24, 2017 at 13:57

calamities · Accepted Answer · 2017-10-25 01:32:14Z

1

Here is a super easy alternative...

SELECT CAST(left('2017-09-06 00:36:32.473491+05:30',19) as datetime)

answered Oct 25, 2017 at 1:32

calamities

783 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mazhar · Accepted Answer · 2017-10-24 13:55:36Z

1

You need to strip out the milliseconds and time offset before you can CAST to DATETIME.

DECLARE @s NVARCHAR(100) = '2017-09-06 00:36:32.473491+05:30'
SELECT CONVERT(NVARCHAR(19), @s, 121)
SELECT CAST(CONVERT(NVARCHAR(19), @s, 121) AS DATETIME)

However, if your datatype was DATETIME2 then you wouldn't need to

DECLARE @s NVARCHAR(100) = '2017-09-06 00:36:32.473491+05:30' SELECT
CONVERT(NVARCHAR(25), @s, 121) SELECT CAST(CONVERT(NVARCHAR(25), @s,
121) AS DATETIME2)

answered Oct 24, 2017 at 13:55

Mazhar

3,8431 gold badge14 silver badges30 bronze badges

Comments

Hamed · Accepted Answer · 2017-10-24 13:58:55Z

0

try this CONVERT(DATETIME,'2017-09-06 00:36:32.473') do the job but not with your precision!

answered Oct 24, 2017 at 13:58

Hamed

1683 silver badges17 bronze badges

Collectives™ on Stack Overflow

MS SQL Server String to Datetime

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related