Hello - If you have a variable declared and as a 'date' format will that accept date entries when you set the variable as MM/DD/YYYY and/or MM/DD/YY?
Thank you!!
did you try it? Try things before posting if you can
Yes. The information is on the MSDN site, but here is an example from code:
--Last day of last month: DECLARE @EndDate VARCHAR(10) SET @EndDate = CONVERT(VARCHAR(10), DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1), 101) --PRINT @EndDate
In this case, I am using the function GETDATE() to fill the string. Topics to look up include date formatting, CONVERT and CAST. I use "T-SQL format date" as a web search phrase.
I think its a really bad idea. Sadly SQL will accept pretty much anything as a date / datetime but it uses parsing rules which will change depending on both Server Settings (such as Locale) and also currently connected user settings (such as Language), so my advice would be to use an UNambiguous date format.
SQL ALWAYS considers "yyyymmdd" to be unambiguous, regardless of whether the Server / User settings suggest d/m/y or m/d/y etc. so my advice would be to stick to that when presenting date in String Format to SQL (for conversion, implicit or not) to a Date / DateTime variable / column.
The other Unambiguous date format is the ISO format "yyyy-mm-ddThh:mm:ss" - you need both the punctuation and the "T". Seconds may be optional (I can't remember), milliseconds are definitely optional.
P.S. If you know that a string date is in D/M/Y or M/D/Y (ro some other) format you can use
SET DATEFORMAT dmy
to force SQL's parsing algorithm to what you need it to do. But SQL will still parse a 6-digit or 8-digit string unambiguously as "yymmdd" or "yyyymmdd" regardless of DATEFORMAT setting.
YYYY-MM-DD is UNambiguous for DATE datatype.
In addition to what Kristen said, also read this http://beyondrelational.com/modules/2/blogs/70/posts/10898/understanding-datetime-column-part-ii.aspx
That is true, but here we have taken the view that because we still have DATETIME datatypes in our code that it is "high risk" to use "YYYY-MM-DD" for DATE datatype incase we accidentally use it for DATETIME datatype, and therefore we are only using "YYYYMMDD" - which is unambiguous for DATE and DATETIME.
Minefield!