0

Dates are not inserting correctly to table, any explanation / solution?

create table test 
( 
    ID bigint,
    MarketOpen datetime
);

insert into test (ID, MarketOpen) 
values (1, 2019-01-19-11-40-00);

select * from test;

Fiddle

Dallas
  • 35
  • 1
  • 9

1 Answers1

4

Thats totally the wrong way to enter a date. SQL Server is treating your current syntax as a calculation e.g. 2019-01-19-11-40-00=1948 and then converting the number 1948 to a datetime. You need to use a formatted string e.g.

insert into #test (ID, EventId, MarketId, RaceDate, MarketOpen, HorseID)
values 
(1, 123, 153722767, '2019-01-19 11:40:00', '2019-01-18 11:40:00', 34434);

Note: As mentioned by seanb its best practice to use a non-ambiguous format when specifying dates and the ISO format (yyyymmdd) is probably the best of these.

Dale K
  • 25,246
  • 15
  • 42
  • 71
  • 2
    Note for generality, you should use '20190110' rather than '2019-01-10'. As @AaronBertrand pointed out to me, 2019-01-10 is not universal e.g., [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=2f2981f11a52e64f13ef1e3e654f6e07) – seanb Oct 08 '20 at 03:32
  • 2
    Personally, I prefer the more human-readable version of ISO8601 - `yyyy-mm-ddThh:mm:ss`, but that's really a personal preference. Note that when using the `DateTime2` data type, you can safely use `yyyy-mm-dd hh:mm:ss`. For more information, read [Shnugo's question and it's answers](https://stackoverflow.com/q/45792766/3094533) – Zohar Peled Oct 08 '20 at 05:38