4

Im trying to find a set of results in a database based on dates. The dates are stored as varchars in the format dd/mm/yyyy hh:mm:ss.

What i would like to do is search for all dates within a range of specified dates.

For example i tried:

SELECT * FROM table_name
WHERE fromDate BETWEEN LIKE '%12/06/2012%' AND LIKE '%16/06/2012%'

Is something like this possible or is there a better way of doing this type of statement, because so far i have had little success?

I'm using Microsoft SQL server 2008.

Peter

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Peter
  • 521
  • 2
  • 6
  • 20
  • 6
    Is there a reason for storing the dates as VARCHARS? http://stackoverflow.com/questions/4759012/when-to-use-varchar-and-date-datetime – infojolt Jun 21 '12 at 11:39
  • 3
    And `varchars` in an ambiguous and unsorted format at that. An index on that column will be useless for date range queries spanning more than one day. – Martin Smith Jun 21 '12 at 11:41
  • 2
    And if the values have times, you don't want to use `BETWEEN` anyway - https://sqlblog.org/blogs/aaron_bertrand/archive/2011/10/19/what-do-between-and-the-devil-have-in-common.aspx - but you really need to fix that in any case. Store datetime values as datetime, not as varchar. – Aaron Bertrand Jun 21 '12 at 12:14
  • Unfortunately i do not have permission/access to change the types in the fields, at least not at this current point in time, thats why i was looking for this workaround, but thanks. – Peter Jun 21 '12 at 12:16
  • 3
    You should pass the information along to the people who do have permissions/access, because they may not know how wrong they're doing it. – Aaron Bertrand Jun 21 '12 at 12:54
  • If possible, you want to store the dates as DATE/DATETIME. This will allow you to remove the need for the like statement. – infojolt Jun 21 '12 at 11:40
  • Yep, i know date/datetime would be best way to store them, but its not my database and i dont have privileges to change the types, but i will try make a suggestion to DBA.. but thanks for response. – Peter Jun 21 '12 at 12:18

6 Answers6

7

Since your date values also include time, you can't use BETWEEN. The only safe way to do this is:

SELECT <cols> FROM dbo.table_name
WHERE CONVERT(DATE, fromDate, 103) >= '20120612' 
AND CONVERT(DATE, fromDate, 103) < '20120617';

But as Martin noticed, you'll never be able to use an index on that column, so this will always perform a full table scan.

If you really, really want to use BETWEEN, converting to DATE is the only safe way to do so (well, or trimming the time off in other, less efficient ways):

SELECT <cols> FROM dbo.table_name
WHERE CONVERT(DATE, fromDate, 103) BETWEEN '20120612' AND '20120616';

But for consistency reasons I recommend against between even in that case.

Community
  • 1
  • 1
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Quick question, im getting values returned that are in May, ex: 18/05/2012, would you know why that is? using: SELECT FROM dbo.table_name WHERE CONVERT(DATE, fromDate, 103) >= '20120612' AND CONVERT(DATE, fromDate, 103) < '20120617'; – Peter Jun 21 '12 at 13:29
  • @Peter can you show the actual string that is in the table? P.S. this is one of the problems with allowing any old crap in a column of the wrong data type. – Aaron Bertrand Jun 21 '12 at 13:35
  • RunStartTime 06/06/2012 18:28:48 RunEndTime 08/06/2012 05:05:58 This is a sample of 2 fields within the DB table that are returned. – Peter Jun 21 '12 at 13:47
  • Can you post to the question (not in a comment) the `CREATE TABLE` statement that includes these two columns and their data types, `INSERT` statements for the rows in question, and the *actual* query you're running. I can't reproduce but then again I don't know exactly what you have in front of you. For example you seem to reference two different columns here. – Aaron Bertrand Jun 21 '12 at 14:17
2

Try This

SELECT * FROM table_name
WHERE Convert(Date,fromDate,103) 
BETWEEN '20120612' AND '20120616'
Asif
  • 2,657
  • 19
  • 25
  • 2
    Since the datetime column has time as well, wouldn't >= 20120612 and < 20120617 be more accurate? – Aaron Bertrand Jun 21 '12 at 12:15
  • no if you try this code it will work with date or datetime as well – Asif Jun 21 '12 at 12:22
  • No, it won't. Did *you* try it? Put a datetime value like '20120616 01:30' in your table. Does it come back in your result? NO. – Aaron Bertrand Jun 21 '12 at 12:23
  • you are right its not working with '20120616 01:30' but its working with '12/06/2012 10:12:00' try it. – Asif Jun 21 '12 at 12:26
  • 2
    You're missing the point. The OP wants dates from any time on 6/12 through any time on 6/16. How can you assert this is a correct answer if it doesn't return the correct results? – Aaron Bertrand Jun 21 '12 at 12:28
  • yes you are right its not considering the time. i have made amendment to my answer. Thanks to you. – Asif Jun 21 '12 at 12:48
1

The best solution is to store your varchars as DateTime in the database.

Second best is to convert then to dates in the select (as the other answers just indicates so I am not going to give the example)

Pleun
  • 8,856
  • 2
  • 30
  • 50
1

hi man this is not a rocket science bro just make a logic like

Blockquote

SELECT * FROM table_name WHERE fromDate BETWEEN '2021-05-01 12:00:00' AND '2021-05-01 23:59:00'

Blockquote

it will work I stuck too but it helps. i was also trying make a logic like u

Rizz wann
  • 21
  • 3
0

The following works fine for me. Try this:

 SELECT *
 FROM [MABH-Desi-Dera].[dbo].[Order] 
 WHERE (CONVERT(nvarchar,[Order_ID]) BETWEEN  '200622%' AND '200623%')

Here the Order_IDs start with some Date:

derHugo
  • 83,094
  • 9
  • 75
  • 115
Engr. Khuram Shahzad
  • 380
  • 3
  • 13
0

use this

strong textSELECT * FROM table_name WHERE fromDate BETWEEN '2021-05-01 00:00:00' AND '2021-05-01 23:59:00'

X Body
  • 19
  • 3