How I can insert with a query a date like this? 2015-06-02T11:18:25.000
I have tried this:
INSERT INTO TABLE (FIELD) VALUES (convert(datetime,'2015-06-02T11:18:25.000'))
But I have returned:
Conversion failed when converting date and/or time from character string.
I tried also:
CONVERT(DATETIME, '2015-06-02T11:18:25.000', 126)
but it is not working:
Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string.
The entire query is:
INSERT INTO BOLLE_TEST_POPPER (QIDDIADE,QNUMBOLLA,QSELEZIONALE,QDATA,QORA,QPRIMAPESATA,QSECONDAPESATA,QIMP1,QIMP2,QIDCAUSALE,QIDCLIENTE,QIDDESTINAZIONE,QIDVETTORE,QIDSUBVETTORE,QIDCAMION,QORATRASITO,QNUMBOLLAINGRESSO,QDATABOLLAINGRESSO,QCOMMITTENTIDELTRASPORTO,QANNOTAZIONI,QANNOTAZIONIINBOLLA,QIDARTICOLO,QQANTITA,QIDAUTISTA,QNUMTESSERA,QNUMGETTONE,VALORETAB1,VALORETAB2,VALORETAB3,VALORETAB4,VALORETAB5,VALORETAB6,VALORETAB7,VALORETAB8,VALORETAB9,VALORETAB10,VALORETESTO1,VALORETESTO2,VALORETESTO3,VALORETESTO4,VALORETESTO5,VALORETESTO6,VALORETESTO7,VALORETESTO8,VALORETESTO9,VALORETESTO10) VALUES ('4','5234','-',
convert(datetime,'2015-06-02'),convert(datetime,'2015-06-02T11:18:25.000',126),'30020','20230','null','null','4','1','391','50','50','50','500',convert(datetime,'2015-06-02T11:14:06+02:00',126),'-','false','-','-','19','9790.00','1','BK994P','-','-','null','null','null','null','null','null','null','null','null','-','-','-','-','-','-','-','-','-','-');
What is wrong?
Advertisement
Answer
Try this:
INSERT INTO TABLE (FIELD) VALUES CONVERT(DATETIME, '2015-06-02T11:18:25.000', 126)
126 relates to ISO8601, which is the format yyyy-mm-ddThh:mi:ss.mmm.
This is the same format as the string '2015-06-02T11:18:25.000'.
For more information, see here.
For dates with a datetimeoffset (for example '2015-06-02T11:14:06+02:00' – note the +02:00 at the end), you will have to do this:
CONVERT(DATETIME, CONVERT(DATETIMEOFFSET,'2015-06-02T11:14:06+02:00'), 127)
The fully fixed query should be:
INSERT INTO BOLLE_TEST_POPPER (QIDDIADE,QNUMBOLLA,QSELEZIONALE,QDATA,QORA,QPRIMAPESATA,QSECONDAPESATA,QIMP1,QIMP2,QIDCAUSALE,QIDCLIENTE,QIDDESTINAZIONE,QIDVETTORE,QIDSUBVETTORE,QIDCAMION,QORATRASITO,QNUMBOLLAINGRESSO,QDATABOLLAINGRESSO,QCOMMITTENTIDELTRASPORTO,QANNOTAZIONI,QANNOTAZIONIINBOLLA,QIDARTICOLO,QQANTITA,QIDAUTISTA,QNUMTESSERA,QNUMGETTONE,VALORETAB1,VALORETAB2,VALORETAB3,VALORETAB4,VALORETAB5,VALORETAB6,VALORETAB7,VALORETAB8,VALORETAB9,VALORETAB10,VALORETESTO1,VALORETESTO2,VALORETESTO3,VALORETESTO4,VALORETESTO5,VALORETESTO6,VALORETESTO7,VALORETESTO8,VALORETESTO9,VALORETESTO10) VALUES ('4','5234','-',
convert(datetime,'2015-06-02'),convert(datetime,'2015-06-02T11:18:25.000',126),'30020','20230','null','null','4','1','391','50','50','50','500',CONVERT(DATETIME, CONVERT(DATETIMEOFFSET,'2015-06-02T11:14:06+02:00'), 127),'-','false','-','-','19','9790.00','1','BK994P','-','-','null','null','null','null','null','null','null','null','null','-','-','-','-','-','-','-','-','-','-');