# SQL Syntax. Table.Date \< '5.1.2023'

**URL:** <https://boards.straightdope.com/t/sql-syntax-table-date-5-1-2023/984634>\
**Category:** Factual Questions\
**Created:** [May 26, 2023, 2:50pm UTC](https://boards.straightdope.com/t/sql-syntax-table-date-5-1-2023/984634 "2023-05-26T14:50:21Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![AHunter3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ahunter3/32/368_2.png) [@AHunter3](https://boards.straightdope.com/u/AHunter3)\
**Post date:** [May 26, 2023, 2:50pm UTC](https://boards.straightdope.com/t/sql-syntax-table-date-5-1-2023/984634/1 "2023-05-26T14:50:21Z")

</div>

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

something else?

---

<div class="post-metadata">

**Author:** ![Dorjan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dorjan/32/12440_2.png) [@Dorjan](https://boards.straightdope.com/u/Dorjan)\
**Post date:** [May 26, 2023, 3:00pm UTC](https://boards.straightdope.com/t/sql-syntax-table-date-5-1-2023/984634/2 "2023-05-26T15:00:00Z")

</div>

How about:

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)

---

<div class="post-metadata">

**Author:** ![AHunter3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ahunter3/32/368_2.png) [@AHunter3](https://boards.straightdope.com/u/AHunter3)\
**Post date:** [May 26, 2023, 3:04pm UTC](https://boards.straightdope.com/t/sql-syntax-table-date-5-1-2023/984634/3 "2023-05-26T15:04:33Z")

</div>

Thanks!!! I assume I need to convert it if activestartdate is _stored as_ a date, yes?

What does the 120 do?

---

<div class="post-metadata">

**Author:** ![Dorjan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dorjan/32/12440_2.png) [@Dorjan](https://boards.straightdope.com/u/Dorjan)\
**Post date:** [May 26, 2023, 3:15pm UTC](https://boards.straightdope.com/t/sql-syntax-table-date-5-1-2023/984634/4 "2023-05-26T15:15:47Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![AHunter3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ahunter3/32/368_2.png) [@AHunter3](https://boards.straightdope.com/u/AHunter3)\
**Post date:** [May 26, 2023, 3:25pm UTC](https://boards.straightdope.com/t/sql-syntax-table-date-5-1-2023/984634/5 "2023-05-26T15:25:07Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\
**Post date:** [May 26, 2023, 4:07pm UTC](https://boards.straightdope.com/t/sql-syntax-table-date-5-1-2023/984634/6 "2023-05-26T16:07:49Z")

</div>

If it’s Access, the date delimiter is ‘#’

And the default date format is M/D/YYYY, although this will depend upon your computer’s Regional Settings

Select some, columns From atable Where Date\_Column \>= #5/1/2023#

---

<div class="post-metadata">

**Author:** ![md-2000](https://avatars.discourse-cdn.com/v4/letter/m/9d8465/32.png) [@md-2000](https://boards.straightdope.com/u/md-2000)\
**Post date:** [May 26, 2023, 4:40pm UTC](https://boards.straightdope.com/t/sql-syntax-table-date-5-1-2023/984634/7 "2023-05-26T16:40:33Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![AHunter3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ahunter3/32/368_2.png) [@AHunter3](https://boards.straightdope.com/u/AHunter3)\
**Post date:** [May 26, 2023, 5:16pm UTC](https://boards.straightdope.com/t/sql-syntax-table-date-5-1-2023/984634/8 "2023-05-26T17:16:42Z")

</div>

I got it working with the #05/01/2023# format!

---

<div class="post-metadata">

**Author:** ![Dorjan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dorjan/32/12440_2.png) [@Dorjan](https://boards.straightdope.com/u/Dorjan)\
**Post date:** [May 26, 2023, 5:24pm UTC](https://boards.straightdope.com/t/sql-syntax-table-date-5-1-2023/984634/9 "2023-05-26T17:24:21Z")

</div>

Ahh, Access. Glad you figured it out!

---

<div class="post-metadata">

**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\
**Post date:** [May 26, 2023, 8:47pm UTC](https://boards.straightdope.com/t/sql-syntax-table-date-5-1-2023/984634/10 "2023-05-26T20:47:30Z")

</div>

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”

---

<div class="post-metadata">

**Author:** ![AHunter3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ahunter3/32/368_2.png) [@AHunter3](https://boards.straightdope.com/u/AHunter3)\
**Post date:** [May 26, 2023, 9:20pm UTC](https://boards.straightdope.com/t/sql-syntax-table-date-5-1-2023/984634/11 "2023-05-26T21:20:52Z")

</div>

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!”

---

<div class="post-metadata">

**Author:** ![scudsucker](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/scudsucker/32/14101_2.png) [@scudsucker](https://boards.straightdope.com/u/scudsucker)\
**Post date:** [May 26, 2023, 11:31pm UTC](https://boards.straightdope.com/t/sql-syntax-table-date-5-1-2023/984634/12 "2023-05-26T23:31:03Z")

</div>

> [@Dorjan](#):
>
> The style code `120` stands for the ‘YYYY-MM-DD’ format

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.

---

<div class="post-metadata">

**Author:** ![Dr.Strangelove](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dr.strangelove/32/6613_2.png) [@Dr.Strangelove](https://boards.straightdope.com/u/Dr.Strangelove)\
**Post date:** [May 26, 2023, 11:42pm UTC](https://boards.straightdope.com/t/sql-syntax-table-date-5-1-2023/984634/13 "2023-05-26T23:42:00Z")

</div>

Oblig.  
[![](https://imgs.xkcd.com/comics/iso_8601.png) ](https://imgs.xkcd.com/comics/iso_8601.png)

> [@AHunter3](#):
>
> Yeah, I didn’t realize that something as fundamental as how you handle date format would be proprietary to different implementations.

Ah ha ha ha ha!

Time/date handling was broken long before it got to computers. And then computers added their own layers of brokenness on top of it.

---

<div class="post-metadata">

**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\
**Post date:** [May 27, 2023, 12:14am UTC](https://boards.straightdope.com/t/sql-syntax-table-date-5-1-2023/984634/14 "2023-05-27T00:14:07Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![AHunter3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ahunter3/32/368_2.png) [@AHunter3](https://boards.straightdope.com/u/AHunter3)\
**Post date:** [May 27, 2023, 12:39am UTC](https://boards.straightdope.com/t/sql-syntax-table-date-5-1-2023/984634/15 "2023-05-27T00:39:22Z")

</div>

> [@Dr.Strangelove](#):
>
> Time/date handling was broken long before it got to computers. And then computers added their own layers of brokenness on top of it.

I meant SQL not computers in general. I figured SQL in even its most primitive form would have established how it — SQL — would handle date inputs.

---

<div class="post-metadata">

**Author:** ![Dr.Strangelove](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dr.strangelove/32/6613_2.png) [@Dr.Strangelove](https://boards.straightdope.com/u/Dr.Strangelove)\
**Post date:** [May 27, 2023, 1:11am UTC](https://boards.straightdope.com/t/sql-syntax-table-date-5-1-2023/984634/16 "2023-05-27T01:11:05Z")

</div>

Date/time incompatibility is fractal in nature. No matter how narrow a slice you look at, there is still infinite complexity.

I did have a laugh at the [Wiki page](https://en.wikipedia.org/wiki/SQL):

> 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](https://en.wikipedia.org/wiki/Case_sensitivity) vary from vendor to vendor.

There you go. It’s more what you’d call _guidelines_ than an actual standard.

---

<div class="post-metadata">

**Author:** ![crazyjoe](https://avatars.discourse-cdn.com/v4/letter/c/f14d63/32.png) [@crazyjoe](https://boards.straightdope.com/u/crazyjoe)\
**Post date:** [May 27, 2023, 3:23am UTC](https://boards.straightdope.com/t/sql-syntax-table-date-5-1-2023/984634/17 "2023-05-27T03:23:06Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![AHunter3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ahunter3/32/368_2.png) [@AHunter3](https://boards.straightdope.com/u/AHunter3)\
**Post date:** [May 27, 2023, 3:42am UTC](https://boards.straightdope.com/t/sql-syntax-table-date-5-1-2023/984634/18 "2023-05-27T03:42:53Z")

</div>

> [@crazyjoe](#):
>
> 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.

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.

---

<div class="post-metadata">

**Author:** ![Dorjan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/dorjan/32/12440_2.png) [@Dorjan](https://boards.straightdope.com/u/Dorjan)\
**Post date:** [May 27, 2023, 1:16pm UTC](https://boards.straightdope.com/t/sql-syntax-table-date-5-1-2023/984634/19 "2023-05-27T13:16:06Z")

</div>

> [@scudsucker](#):
>
> 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.

Amen.

---

<div class="post-metadata">

**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\
**Post date:** [May 27, 2023, 6:10pm UTC](https://boards.straightdope.com/t/sql-syntax-table-date-5-1-2023/984634/20 "2023-05-27T18:10:30Z")

</div>

As to ISO8601.

If you’re performing computations or comparisons on a stringly value the terrorists have already won.

ISO8601 is a fine wire format. It’s not a display format, it’s not a storage format, and it’s certainly not a computational / comparison format.

[Next page](https://boards.straightdope.com/t/sql-syntax-table-date-5-1-2023/984634.md?page=2)