Just like the title says. My professor marked points off of all my queries because I queried date data type in this format: yyyymmdd. Example; Date between yyyymmdd. Instead of yyyy-mm-dd.
I just want to know if there is any argument I can make for the points because it seems pretty trivial since the query still worked.
A good argument against using yyyy-mm-dd (with no time) is that, depending on language/DMY settings1, SQL Server can get it wrong, resulting in incorrect queries2 or errors:
set language british
select * from sys.objects where create_date between '2001-05-01' and '2001-05-30'
Produces the error
Msg 242, Level 16, State 3, Line 3
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
This is because, for whatever reason, SQL Server thinks that the british use yyyy-dd-mm (We do not).
yyyymmdd is unamibiguously interpreted by SQL Server no matter what language settings are in force.
Note that this is SQL Server specific.
1Note that I explicitly change a setting in this script but even without it, some user's default settings may cause the same issue.
2Such as Suraj Kumar's example from the comments:
select * from sys.objects where create_date between '2001-05-01' and '2001-05-11'
Which queries between 5th January and 5th November, not between 1st May and the 11th.