DateTime Syntax Error

All we need is an easy explanation of the problem, so here it is.

I am building a very basic database in SSMS, one table of which will hold "sleep instances" recording the time of falling asleep and waking up. I have created the table with three columns: ID (int), StartTime (DateTime), EndTime (DateTime). I am now trying to add the first row:

INSERT INTO SleepInstances
VALUES (1, 2022-07-05 22:20:00, 2022-07-06 06:40:00);

And I get a syntax error on the StartTime hour (22). As far as I can tell it is formatted correctly and identically to the EndTime value, which gives me no errors.

The only answers I can find about time issues seem to be related to time zones, which I can’t imagine being the issue here as I get the error no matter what hour I try.
I am very new to SQL, so all of this is based off W3 Schools page on date functions.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You specify datetime literals as a string in SQL Server. I.e., enclose it in single-quotes. It should be something along the lines of below:

INSERT INTO SleepInstances
VALUES (1, '2022-07-05 22:20:00', '2022-07-06 06:40:00');

However, since the you say that the data type is datetime, you should use a language neutral datetime format, like for instance below:

INSERT INTO SleepInstances
VALUES (1, '2022-07-05T22:20:00', '2022-07-06T06:40:00');

If the data type would have been datetime2, then the first format would also have been language neutral. I have elaborated on this and much more in this article.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply