How to query only rows containing date data between two dates.

[Luk3r]

Okay, so I've been all over Google without much luck. I'm trying to query a column that contains dates in this format (1/1/2014, 1/2/2014, etc.). The column is nchar (which I'm aware should be changed), but I didn't figure this would be an issue. What's happening is when I search for a date starting at 1/1/2014, the query also returns results with 1/10/2014, 1/11/2014, and so on. Any help would be much appreciated! Here's the simple query I was using:

select *
from WrapUpDataTable1
where WrapUpDataTable1.Date >= '1/1/2014'
  and WrapUpDataTable1.Date <= '1/3/2014'

[NeoPa]

Unless you have a very good reason for not doing so, I would suggest you follow the more straightforward approach of storing the data you want. IE. Dates rather than strings that look like dates.

If you must use a string value then you should, at least, format it in such a way that it is compatible regardless of the values (EG. mm/dd/yyyy rather than m/d/yyyy).

If you must have a string and would rather have an easier life, don't use a standard date format at all, but something that orders the values left-to-right as necessary for sorting. EG. yyyy/mm/dd.

I would imagine that extracting the values you want from your strings using T-SQL would be quite painful and messy.

[Luk3r]

Thanks, NeoPa. I was hoping that wasn't the only way to go about this, but it makes the most sense. I had/have every intention of changing from string to date format. This was more of a "why doesn't it work?" and less of a "how do I make it work?"...since I understand that the table is already set up incorrectly :) Thanks a lot for the response!

[NeoPa]

Are you still interested in more explanations of how/why the alternative suggestions would work?

It's perfectly reasonable to ask technical questions that you aren't, necessarily, considering putting into practice. If you want further understanding then I'm happy to oblige.

I would always explain the caveat that it isn't a good approach though. Even if you appreciate that already, many that find and read this later on may not.

[Luk3r]

NeoPa, I was more interested in knowing if there was an answer to my question while using string instead of date. I wasn't expecting much, except to be told that I should be using Date, and possibly a 'workaround' until I can recreate this table. But if there is no way to work around it, then I will just take my time and change that column to date format when I get a chance. I appreciate your offer to help, though! :)

[NeoPa]

There is a way to handle that in T-SQL. It would be very fiddly though, due to the unpredictabilit y of where the relevant data is within the string. The month parts start in different positions for 1/1/2013 and 12/31/2013, for instance.

A fairly long and involved formula could be set up which would use the following functions :
RIGHT(), CHARINDEX() & SUBSTRING().

[Rabbit]

A workaround would be to use the CDate function to convert the strings into a date. Assuming of course that the string format matches the system format.

[NeoPa]

I'm not sure T-SQL has a CDate() function. You may find using CONVERT() is an easier approach than what i was thinking of mind. Good idea Rabbit. My T-SQL is pretty rusty now TBF.

[Rabbit]

Oh right, this is the SQL Server forum. For some reason I thought it was VBA, yes, CONVERT or CAST could be used as a workaround if you are limited to strings.

[Luk3r]

Thanks, Rabbit and NeoPa. I'll look into CONVERT and CAST simply for learning purposes. But I'll go ahead and recreate the table with that column only accepting DATES. I appreciate the help.

[NeoPa]

I think what Rabbit means to say is that seeing me post in SQL-Server is very rare nowadays and that led him to assume it must be an Access question :-D

I can sympathise. It's been a while since I ventured into this forum.

@Luk3r
That seems a very sensible approach. It's been a pleasure working with you.