How to insert datetime from string in sql server

Question

Lets say i have a string like Below

string dt = "2/14/2014 11:59:59 PM";

now i want to insert this in sql server 2012. i am trying with the below query

INSERT INTO Some_Info(date_colm1,date_colm2) VALUES(CONVERT(datetime, '2/14/2014 11:59:59 PM'),CONVERT(datetime, '2/14/2014 11:59:59 PM') );

i'm getting output like below

Conversion failed when converting date and/or time from character string.

what seems to be the problem? am i missing something?

Which is the data type of your columns? I suspect that they are `date` instead of `datetime`. — Hamlet Hakobyan, Feb 13 '14 at 11:12

score 2 · Answer 1 · answered Feb 13 '14 at 11:20

2

For sql server 2012 you can use format syntax ("YYYY-MM-DD hh:mm:ss") to insert datetime refer this link

FORMAT ( value, format [, culture ] )

FORMAT (@date, 'YYYY-MM-DD hh:mm:ss','en-US')

answered Feb 13 '14 at 11:20

Mala

1,119
7
4

Shell · Answer 2 · 2014-02-13T11:26:03.443

0

use 'yyyy-MM-dd HH:mm:ss' format ie. '2014-02-13 16:35:45' when you are using Convert function then you should provide date format number also. for example Convert(smalldatetime, '25/12/2014 15:24', 103). 103 is the date format of dd/MM/yyyy HH:mm

edited Feb 13 '14 at 11:26

answered Feb 13 '14 at 11:05

Shell

6,818
11
39
70

Thorsten Kettner · Answer 3 · 2014-02-13T11:58:30.293

To my knowledge, SQL-Server lacks the ability to convert a custom-formatted date string to datetime. It has some built-in formats, but often they don't match the format you have to deal with. So if for instance you get a string like '2/14/2014 11:59:59 PM' from a file, you must modify it with string operations (substring especially) first to get to a built-in format before you can use CONVERT (or even CAST) on it.

Here is the link to CONVERT and its known formats: http://msdn.microsoft.com/de-de/library/ms187928.aspx.

How to insert datetime from string in sql server

3 Answers3