Query comparing dates in SQL

Question

I have a table with dates that all happened in the month November. I wrote this query

select id,numbers_from,created_date,amount_numbers,SMS_text 
from Test_Table
where 
created_date <= '2013-04-12'

This query should return everything that happened in month 11 (November) because it happened before the date '2013-04-12' (in December)

But it's only returning available dates that happened in days lesser than 04 (2013-04-12)

Could it be that it's only comparing the day part? and not the whole date?

How to fix this?

Created_date is of type date

Date format is by default yyyy-dd-MM

Maybe it thinks `2013-04-12`? is April 12th? Or maybe `created_date`is a string and not a date? — jpw, Nov 12 '13 at 08:28
Look at [Cast & Convert](http://msdn.microsoft.com/en-us/library/ms187928.aspx) on T-SQL manual and use the appropriate conversion for your locale — Steve, Nov 12 '13 at 08:30
No need to cast at all, just use the invariant format '20130412' — Panagiotis Kanavos, Nov 12 '13 at 08:31
@Jpw no not April. Because it's returning all the dates in November that happened in days less than 4 — HelpASisterOut, Nov 12 '13 at 08:31
@PanagiotisKanavos Still returning the same result: All the dates in November that happened in days less than 4. — HelpASisterOut, Nov 12 '13 at 08:32
@Steve I already did convert the date to its right format. The problem is that its returning all the dates in November that happened in days less than 4, so its working but its only comparing the day part — HelpASisterOut, Nov 12 '13 at 08:32
Oops, if you want December, you should try '20131204'. Who ever puts the month at the end? — Panagiotis Kanavos, Nov 12 '13 at 08:35
@PanagiotisKanavos '20131204' is returning everything before April — HelpASisterOut, Nov 12 '13 at 08:38
@HelpASisterOut is your date actually a `DATE` or is it something it shouldn't be like `VARCHAR(n)` — T I, Nov 12 '13 at 08:40
@HelpASisterOut what are you using to query the database? SSMS or other? — T I, Nov 12 '13 at 08:45
Instead of sending a string with the date then, try creating a parameterized query and pass the date as a date-typed parameter. BTW what version of SQL Server are you using? DATE was added in SQL Server 2008. — Panagiotis Kanavos, Nov 12 '13 at 10:55
Also, are you SURE you aren't interpretting the result's MONTH as the DAY? What you describe until now is atypical - unless you have mixed months with dates. yyyy-dd-mm is not the default format for date, according [to the documentation](http://technet.microsoft.com/en-us/library/bb630352(v=sql.100).aspx) it is interpreted as yyyy-mm-dd — Panagiotis Kanavos, Nov 12 '13 at 10:56
I parameterized the query and passed the date as a date and it worked. Thank you @PanagiotisKanavos for keeping up — HelpASisterOut, Nov 12 '13 at 10:59

score 110 · Accepted Answer · edited Jun 20 '20 at 09:12

Instead of '2013-04-12' whose meaning depends on the local culture, use '20130412' which is recognized as the culture invariant format.

If you want to compare with December 4^th, you should write '20131204'. If you want to compare with April 12^th, you should write '20130412'.

The article Write International Transact-SQL Statements from SQL Server's documentation explains how to write statements that are culture invariant:

Applications that use other APIs, or Transact-SQL scripts, stored procedures, and triggers, should use the unseparated numeric strings. For example, yyyymmdd as 19980924.

EDIT

Since you are using ADO, the best option is to parameterize the query and pass the date value as a date parameter. This way you avoid the format issue entirely and gain the performance benefits of parameterized queries as well.

UPDATE

To use the the the ISO 8601 format in a literal, all elements must be specified. To quote from the ISO 8601 section of datetime's documentation

To use the ISO 8601 format, you must specify each element in the format. This also includes the T, the colons (:), and the period (.) that are shown in the format.

... the fraction of second component is optional. The time component is specified in the 24-hour format.

@andy not exactly, the ISO8601 format *includes* the time element. Or [as the docs say](https://msdn.microsoft.com/en-us/library/ms187819.aspx), `To use the ISO 8601 format, you must specify each element in the format. This also includes the T`. — Panagiotis Kanavos, Sep 11 '15 at 08:17
Sorry for being unclear, my point was that ISO8601 defines the order just the way you described: YYYY-MM-DD or for short YYYYMMDD. However, as the docs also state: "datetime is not ANSI or ISO 8601 compliant.". The ISO itself would not require a time part. — andy, Sep 11 '15 at 08:27
What you refer to doesn't change the fact that `YYYY-MM-DD` is *not* recognized as ISO 8601. The time parts *are* required. Call it T-SQL's strangeness if you will, or incomplete implementation. It may even be that this was carried over from Sybase — Panagiotis Kanavos, Sep 11 '15 at 08:32

score 36 · Answer 2 · answered Nov 12 '13 at 08:32

36

Try like this

select id,numbers_from,created_date,amount_numbers,SMS_text 
from Test_Table
where 
created_date <= '2013-12-04'

answered Nov 12 '13 at 08:32

Nithesh Narayanan

11,481
34
98
138

9

Culture specific format. Is that 12 April or 4 December? The dash-separated format is NOT the international format – Panagiotis Kanavos Nov 12 '13 at 08:37
It is 4 December, since the server is in that culture , i think we don't want to worry more about culture. Anyway we are not going to change the db server culture recently. Your point is correct but i don't think my answer is wrong. – Nithesh Narayanan Nov 12 '13 at 08:39
2

So what happens when you need to store data from international clients? Or the date comes from a web browser which follows the *user's* culture? The guidelines exist for a reason, and by simply NOT using the wrong format, you avoid all errors – Panagiotis Kanavos Nov 12 '13 at 08:42
2

@Nithesh This is returning April 12th – HelpASisterOut Nov 12 '13 at 08:44
26

@PanagiotisKanavos The dash-separated format **is** the international format ever since [ISO 8601](http://en.wikipedia.org/wiki/ISO_8601) was published in 1988. Other formats are discouraged and [ridiculed](http://xkcd.com/1179/). Even the article you posted, ["Write International Transact-SQL Statements"](http://technet.microsoft.com/en-us/library/ms191307.aspx) never once identifies 'yyyymmdd' as any kind of "international" standard. – Jesse Webb Jul 17 '14 at 17:50
@JesseWebb SQL Server server doesn't interpret dash-separated *dates* as *ISO 8601* unless you pass them as an ODBC timestamp (eg. `{ d '1998-09-24' }`). The article I posted *does* mention this, and *does* say you need to use the unseparated format as the only one that is guaranteed to work when using T-SQL. I quoted the relevant excerpt in my answer. There are multiple SO questions from people that *did* have problems when passing the *date* in a dash-separated format – Panagiotis Kanavos Aug 04 '14 at 13:44
2

Adding single quotes around the date resolved this for me. Was using format 2/19/2015. Changed to '2/19/2015' and it started working. Thanks for the simple idea. – Ethan Turk Feb 19 '15 at 21:26

Bimzee · Answer 3 · 2023-04-11T02:16:23.397

16

If You are comparing only with the date vale, then converting it to date (not datetime) will work

select id,numbers_from,created_date,amount_numbers,SMS_text 
 from Test_Table
 where 
 created_date <= convert(date,'2013-04-12',23)

This conversion is also applicable during using GetDate() function

-- Updating cast number as per the suggestion of @Thomas853

edited Apr 11 '23 at 02:16

answered Apr 13 '16 at 12:35

Bimzee

1,138
12
15

1

Based on https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15, it should be style 23 and not 102. Style 23 is ISO8601 (yyyy-mm-dd) and style 102 is ANSI (yyyy.mm.dd). – Thomas853 Jan 09 '22 at 09:13

score 4 · Answer 4 · answered Nov 12 '13 at 08:32

4

please try with below query

select id,numbers_from,created_date,amount_numbers,SMS_text 
from Test_Table
where 
convert(datetime, convert(varchar(10), created_date, 102))  <= convert(datetime,'2013-04-12')

answered Nov 12 '13 at 08:32

Naresh Pansuriya

2,027
12
15

score 4 · Answer 5 · answered Nov 12 '13 at 08:33

4

You put <= and it will catch the given date too. You can replace it with < only.

answered Nov 12 '13 at 08:33

Kuzgun

4,649
4
34
48

score 1 · Answer 6 · answered Aug 12 '21 at 06:01

Convert them to dates in the same format and then you can compare. Τry like this:

where convert(date, created_date,102) <= convert(date,                                  /*102 is ANSI format*/
                                                    substring('2013-04-12',1,4) + '.' + /*year*/
                                                    substring('2013-04-12',9,2) + '.' + /*month*/
                                                    substring('2013-04-12',6,2)         /*day*/
                                                ,102)

score 0 · Answer 7 · answered Nov 12 '13 at 08:31

0

Date format is yyyy-mm-dd. So the above query is looking for records older than 12Apr2013

Suggest you do a quick check by setting the date string to '2013-04-30', if no sql error, date format is confirmed to yyyy-mm-dd.

answered Nov 12 '13 at 08:31

Bowie

992
3
10
25

score 0 · Answer 8 · edited Jan 31 '17 at 02:03

0

Try to use "#" before and after of the date and be sure of your system date format. maybe "YYYYMMDD O YYYY-MM-DD O MM-DD-YYYY O USING '/ O \' "

Ex:

 select id,numbers_from,created_date,amount_numbers,SMS_text 
 from Test_Table
 where 
 created_date <= #2013-04-12#

edited Jan 31 '17 at 02:03

bfhd

353
9
14

answered Jul 15 '16 at 17:40

Ren Marín

9
1

score 0 · Answer 9 · answered Aug 14 '21 at 13:23

0

you can also use to_char(column_name, 'YYYY-MM-DD) to change format

answered Aug 14 '21 at 13:23

Rahul Gupta

26
1

score 0 · Answer 10 · answered Nov 08 '21 at 15:12

0

Below query can be used to find the records of month November for the year 2013.

Select id,numbers_from,created_date,amount_numbers,SMS_text 
from Test_Table
where Month(created_date) = 11 and Year(created_date) = 2013

answered Nov 08 '21 at 15:12

karthik kasubha

392
2
13

score 0 · Answer 11 · edited Feb 20 '23 at 12:49

0

For my queries on MS Access, I can compare dates with this syntax:

SELECT COUNT(orderNumber) AS Total
FROM Orders
WHERE orderDate >=#2003/04/01#
AND orderDate <=#2003/06/30#;

Where the output is the number of orders between 2003-04-01 and 2003-06-30.

edited Feb 20 '23 at 12:49

Thom A

88,727
11
45
75

answered Feb 17 '23 at 21:36

Hardboiled24

1
2

Query comparing dates in SQL

11 Answers11

Linked