-1

I'm trying to select all results of a table whose date is between 2 specific dates like: 11/04/2019 10:20:32 AND 11/04/2019 12:22:34 for example...

I've tried something like this:

SELECT * 
FROM myTable 
WHERE myDate >= '11/04/2019 10:20:32' 
AND myDate <= '11/04/2019 12:22:34';

This query doesn't return any results and in my point of view is corrected. The problem I see here is the fact that is not assuming the right format of date.

So my main answer is: How can I "convert" that date to verify if it's bigger or smaller?

What I've already tried:

How do I query for all dates greater than a certain date in SQL Server?

Simple DateTime sql query

None of this solutions worked.

EDIT

Using what @Gordon Linoff and the other guys said, I make a search by ID just to see what format is returning on query result and verify that the column of date is returning this: 2019-04-10 10:47:48.000 (datetime)

But when I see all the results of table I verify the column shows this format: 10/04/2019 10:47:48 actually don't know why...

So i've changed my query to this:

SELECT * 
FROM myTable 
WHERE myDate >= '2019-04-10 10:00:00.000' 
AND myDate <= '2019-04-10 11:00:00.000';

And worked! Thanks for the help of everyone.

Mikev
  • 2,012
  • 1
  • 15
  • 27
  • 1
    Try using standard date formats like '2019-04-011 10:20:32'. – Gordon Linoff Apr 11 '19 at 15:30
  • But in my database the format is with the other format and I can't change that... – Mikev Apr 11 '19 at 15:31
  • 2
    @Mikev datetime datatypes don't have a format. Using the ISO (or an unambiguous)format would mean that the string value can't be misinterpreted by the RDBMS. – Thom A Apr 11 '19 at 15:31
  • @Larnu Are you saying that the only format of datetime is '2019-04-11 10:20:32' for example? – Mikev Apr 11 '19 at 15:33
  • SQL Server stores dates as [two number values](http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-are-dates-stored-in-sql-server/) and the conversion is done either explicitly or implicitly. – Jacob H Apr 11 '19 at 15:35
  • @Mikev dates have no formats, they are binary values just like integers, doubles, decimals. – Panagiotis Kanavos Apr 11 '19 at 15:35
  • @JacobH it doesn't. The old `datetime` is actualy a floating point whose integer part is the offset from a set date and the fractional part is the time of day. The newer types don't work that way. – Panagiotis Kanavos Apr 11 '19 at 15:36
  • What I'm saying, @Mikev, is that the value of your dates, in your actual data, is a binary value. When you use an application (like SSMS) it transforms it into a readable format (like `yyyy-MM-dd`). There are only a few unambigious formated for string representations of date(time)s in SQL Server. 2 of which are `yyyyMMdd` and `yyyy-MM-ddThh:mm:ss.sssssss`. – Thom A Apr 11 '19 at 15:37
  • @Larnu So the format of "dd/mm/yyyy hh:mm:ss" doesn't like "exist"? – Mikev Apr 11 '19 at 15:38
  • @Mikev just don't use strings. Use `datetime` or `datetime2(0)` for the columns, use date-typed parameters for the values so you won't have to pass *any* strings*. If you need to pass strings, *don't* use localized strings. Why do you want to search for November 4 2019 anyway? Yes, I know you don't mean that but how can the *computer* guess that? Either pass a date parameter or use an ISO8601 string , ie `2019-04-11T12:22:33` – Panagiotis Kanavos Apr 11 '19 at 15:40
  • What do you mean "exist"? As a string representation of a date, yes, it does. `SELECT CONVERT(varchar(10),GETDATE(),103) + ' ' + CONVERT(varchar(8), GETDATE(),114)` would give you that format, for example. but that's not a date(time) datatype, it's a `varchar`. – Thom A Apr 11 '19 at 15:41
  • I want to add one method to test it. You can change the where clause to some date you know which can also return the result correctly. Then change the format until it can return the same results. I hope it can help. – Gen Wan Apr 11 '19 at 15:50
  • I've edited the answer, I've actually found the solution! Thanks all of you. – Mikev Apr 11 '19 at 16:00

2 Answers2

0

try this, by converting column to datetime convert(datetime, myDate)

SELECT * 
FROM #tbl 
WHERE convert(datetime, myDate, 103) >= convert(datetime, '11/04/2019 10:20:32', 103 )
AND convert(datetime,myDate , 103) <= convert(datetime, '11/04/2019 12:22:34', 103);
Rima
  • 1,447
  • 1
  • 6
  • 12
  • Possibly safer to specify the [*style* integer](https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017) inside `convert` since OP appears to use day-first and not the default month-first dates. – Parfait Apr 11 '19 at 15:37
  • That still leaves two ambiguous string literals. Is that April 11 or November 4 ? Either *all* strings should be converted, or, the far better option, the columns should be changed to `datetime2(0)` or even `datetime` and the values should be passed as strongly-typed date variables, or ISO8601 strings – Panagiotis Kanavos Apr 11 '19 at 15:37
  • Hence why OP needs to specify the *style*. Note: as of now today is April 11 (for most of the world). – Parfait Apr 11 '19 at 15:39
  • @PanagiotisKanavos The format I want is dd/mm/yyyy.. Can I specify that? – Mikev Apr 11 '19 at 15:39
  • 1
    @Mikevn *DON'T* even if you can. First you don't need to, there are safer and *faster* ways to do it. Second, you'll definitely end up mixing dates. – Panagiotis Kanavos Apr 11 '19 at 15:42
  • @Parfait that's a bad idea even though it's possible. The query will end up scanning the entire table. The actual solution is to fix the column types – Panagiotis Kanavos Apr 11 '19 at 15:42
  • Actually the `convert` calls should be on the string not `datetime` field! – Parfait Apr 11 '19 at 15:42
  • fixed by adding style integer. Please check if it is working. – Rima Apr 11 '19 at 15:44
  • @ch2019 ... no need to convert datetime to datetime! – Parfait Apr 11 '19 at 15:45
0

You can always use the operator BETWEEN, for example:

SELECT column_name
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

I'm not sure if you need to convert to datetime though.

Hedgar Bezerra
  • 51
  • 7
  • 1
    Unlikely to actually give the result the OP wants here, as there are times in the data. You'll be better with `>=` and `<` logic. `2019-04-11T00:00:00.0000001` is **not** between `2019-04-01` and `2019-04-11` (but it is more than or equal `2019-04-01` and less than `2019-04-12`) – Thom A Apr 11 '19 at 15:47