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.
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 🙂