SQL Server: Want to use between clause with dates, but dates in string form (YYYY.MM.DD)

Question

Help! One column in my database is for dates. All of my dates are unfortunately in the String form (YYYY.MM.DD). I have a MASSIVE database (300+GB) so ideally would like to avoid transformations.

Is there a way I can select rows for dates in between YYYY.MM.DD and YYYY.MM.DD? What would the script look like?

Thank you!

I wouldn't do that if possible. Can you add a column? Just a calculated one, persisted and indexed :). That way you could have a fast query. Its possible of course to have a string-datetime conversion in runtime but at the cost of performance. — OzrenTkalcecKrznaric, Commented Jul 14, 2013 at 19:57
Fairly obvious question: why are you storing your dates as strings? Fix your schema if at all possible - it'll make your life easier in many, many ways. — Jon Skeet, Commented Jul 14, 2013 at 20:02
@JonSkeet: changing db schema on 300Gb database is very likels to break the layer on top of the db... that was layer created by someone else in my case — OzrenTkalcecKrznaric, Commented Jul 14, 2013 at 20:11
I'm not sure about this, maybe the pros could say if it's a good idea or not.. Why not make a lookup table, in which you have select distinct dates (in string format) from your table, and dates in correct format (created with substring etc. from your current format). After this, just join this new table with your table on the incorrect date format and use the correct date format as a parameter in your where clause. What do you think @JonSkeet? It would give you a quick solution now, but I also suggest changing your schema. — Radu Gheorghiu, Commented Jul 14, 2013 at 20:17

Mike Sherrill 'Cat Recall' · Accepted Answer · 2013-07-14 21:00:06Z

2

If the months and days are stored with leading zeroes, the BETWEEN operator will work as expected. So will ORDER BY.

create table your_table (
  date_value varchar(10) not null
);

insert into your_table values
('2013.01.01'), ('2013.01.13'), ('2013.01.30'), ('2013.01.31'), 
('2013.02.01'), ('2013.02.13'), ('2013.02.28'), ('2013.02.31'), 
('2013.03.01'), ('2013.03.15'), ('2013.03.30'), ('2013.03.31');

select date_value
from your_table
where date_value between '2013.01.01' and '2013-01-31'
order by date_value;

2013.01.01
2013.01.13
2013.01.30

One of the main problems with your structure is that you lose type safety. Look at this query.

select date_value
from your_table
where date_value between '2013.02.01' and '2013.02.31'
order by date_value;

2013.02.01
2013.02.13
2013.02.28
2013.02.31

If you'd used a column of type date or datetime or timestamp, the dbms would not have allowed inserting the values '2013.02.31', because that's not a value in the domain of date. It is a value in the domain of varchar. (And so is "Arrrrgh!", unless you've got a CHECK constraint on that column that severely restricts the acceptable values.)

answered Jul 14, 2013 at 21:00

Mike Sherrill 'Cat Recall'

96.4k20 gold badges132 silver badges195 bronze badges

Hi there. This seems to work. However the following script is pulling the correct # of rows, but is writing them all as Null for some reason: Insert Into dbo.Linked (Description, Amount, Period) Select Description, Amount, coalesce(post_date,transaction_date) FROM table WHERE Description='XXX' AND post_date between '2013.03.25' AND '2013.03.31';
– ZJAY
Commented Jul 14, 2013 at 22:10
What, exactly, does "writing them all as Null" mean here?
– Mike Sherrill 'Cat Recall'
Commented Jul 14, 2013 at 22:30
It says it has pulled 15 rows, but when I open up to view the rows, I get all blanks/nulls. So somehow it is pulling the correct row count between '2013.03.25' AND '2013.03.31', but not writing the information pulled correctly. Any ideas? Thank you.
– ZJAY
Commented Jul 14, 2013 at 22:42
a) Look at the rows the SELECT clause is returning. b) Check the target table for triggers. c) If the data type for the date column in the target table is one of the date types, use an explicit cast in your SELECT clause. (Explicit casts are better than implicit casts.)
– Mike Sherrill 'Cat Recall'
Commented Jul 15, 2013 at 1:27

Add a comment |

slavoo · Accepted Answer · 2013-07-14 20:43:35Z

1

Not good solution, but works (cost much performance).

You have formated date in order year, month, day (good order to compare strings, without transformation to datetime), so you can try

SELECT * FROM Table WHERE StringDate > '2013.07.10' AND StringDate < '2013.07.14'

It returns bad results if there are dates before year 1000 without leading zero ('999.07.14').

But I dont know how it works on big database.

SQL Fiddle

answered Jul 14, 2013 at 20:43

slavoo

6,11664 gold badges40 silver badges41 bronze badges

Not bad solution at all. If OP is ok with adding n/var/char index to his table (extra space cost, possible some extra processing time on dml statements), this could be a satisfying solution. +1
– OzrenTkalcecKrznaric
Commented Jul 14, 2013 at 20:51

Add a comment |

Gordon Linoff · Accepted Answer · 2013-07-14 20:57:12Z

Between in SQL is inclusive of both bounds. If that is what you want, you can just use between:

where col between 'YYYY.MM.DD' and 'YYYY.MM.DD'

Where the two constants are whatever values you are looking for.

If you have an index on the column, then between (as well as >, >=, and so on) will use the index. You do not need to transform the values. If your constants are dates of one form or another, then you can use date_format() to create a string in the right format. For instance, to get dates within the past week:

where col >= date_format(adddate(now(), -7), '%Y.%m.%d')

Collectives™ on Stack Overflow

SQL Server: Want to use between clause with dates, but dates in string form (YYYY.MM.DD)

3 Answers 3

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

Related