I don’t really do SQL. I mean, I muck around in it in minor ways but it’s definitely not my first database language, more of a tertiary acquisition than a secondary, in fact.
I want to modify an existing SQL query that fetches data from organization_contacts and related programs columns. I want to specify that the column programs.activestartdate be a date that is prior to the date that would be the first of the month and year (where the month and the year are hard-coded in the SQL query, in this case year is 2023 and month is 5, hence I want the programs.activestartdate to be less than 5/1/2023.
SELECT yadda.yadda, yadda.moreyadda, FROM programs LEFT JOIN organization_contacts on THIS = THAT
WHERE yadda.this=yadda.that AND so.on AND so.forth
AND programs.activestartdate < 5/1/2023 ? or
AND programs.activestartdate < ‘5/1/2023’ ? or
AND programs.activestartdate < Date (‘5/1/2023’) ? or
AND programs.activestartdate < GetAsDate (‘5.1.2023’) ? or
SELECT yadda.yadda, yadda.moreyadda
FROM programs
LEFT JOIN organization_contacts ON THIS = THAT
WHERE yadda.this=yadda.that AND so.on AND so.forth
AND programs.activestartdate < ‘2023-05-01’
if you need to convert the string to a date:
AND programs.activestartdate < CONVERT(DATE, ‘2023-05-01’, 120)
Whether you need to convert the date to a string depends on the SQL engine you are using. In many systems, comparing a date field with a string formatted as ‘YYYY-MM-DD’ will implicitly convert the string to a date, so you don’t need to do the conversion yourself. However, in some systems, you may need to do the conversion explicitly. It’s usually SQL Server that requires the conversion.
The 120 is specific to SQL Server. The CONVERT function can take an optional third argument which is a style code that dictates how the conversion should be done. The style code 120 stands for the ‘YYYY-MM-DD’ format.
I figured it was a date-format indicator of some sort! Thanks again. It’s accepting the syntax but complaining that some field is a mismatch for the kind of queried value being used; I did the version without the CONVERT statement since this is MS Access and not MS SQL Server. Will futz around with it.
Yes, we had a few issues in the early days of SQL databases, where off-the-shelf programs were originally written for the US market. Our Windows servers were, of course, configured for Canadian settings - the whole country tends to use mm/dd/yyyy except the government, so Windows assumes everyone dones what the Canadian government does - dd/mm/yyyy. It required a matter of adapting the code, or changing the date format used in the data when loading the database.
More often we’d choose to use yyyymmdd which made for much simpler sorts when exported.
Nowadays the computer does the thinking for you… usually.
It always helps when you’re having problems to explain exactly the app & environment you’re using. There are a couple dozen brands and dialects and versions of “SQL”
Yeah, I didn’t realize that something as fundamental as how you handle date format would be proprietary to different implementations.
Reminds me of the first time I learned that plain text format differs depending on whether you’re talking Macintosh, Unix, or Windows. “Whaddaya mean, what kind? It’s plain text!”
The ISO 8601 format, and it shall be kept holy and blessed amongst developers who need to do date/time calculations and wish to remain moderately sane.
When asking database or other programming questions it’s also helpful to include what datatype your variables and database fields are. Some languages are good at auto-converting correctly, and others will demand you handle the conversions explicitly.
SQL implementations are incompatible between vendors and do not necessarily completely follow standards. In particular, date and time syntax, string concatenation, NULL s, and comparison case sensitivity vary from vendor to vendor.
There you go. It’s more what you’d call guidelines than an actual standard.
I’ll go ahead and put this out there, though it’s stretching the topic a bit. In most implementations of SQL, you would want to perform the convert on the literal value, and not the column. You would follow the advice of @Dorjan and NOT do the following:
convert(date,programs.activestartdate) < ‘2023-05-01’
That’s because if you have indexed the column for better searchability, applying a function like CONVERT to it renders the index unusable because the index only works on the “native” value of the column and not one that has been altered in any way.
But that’s a bit of a deep dive into SQL (and the SQL Server version of SQL in particular, but the general notion applies across most implementations).
Nobody was ever going to search that column for anything other than date formats they knew how to input. Access is intimidating and not very powerful for what it gives you. I have no idea why they have it aside from “it was bunded with Microsoft Office”. HIdeous software.
