November 8, 2012 at 12:16 pm
Hello everyone, I have 3 fields, all text strings but all supposed to represent dates.
Field 1: varchar in the format of '20121108'.
Field 2 and 3: varchar string supposed to represent quarters and years. For example, '4Q12' is used to represent 4th quarter of year 2012.
I'm trying to write a query to check if Field 1 is BETWEEN Field 2 and Field 3.
SELECT *
FROM Table
WHERE CONVERT(date,Field1, 111) BETWEEN...
Any help at all would be greatly appreciated.
November 8, 2012 at 12:32 pm
dj1202 (11/8/2012)
Hello everyone, I have 3 fields, all text strings but all supposed to represent dates.Field 1: varchar in the format of '20121108'.
Field 2 and 3: varchar string supposed to represent quarters and years. For example, '4Q12' is used to represent 4th quarter of year 2012.
I'm trying to write a query to check if Field 1 is BETWEEN Field 2 and Field 3.
SELECT *
FROM Table
WHERE CONVERT(date,Field1, 111) BETWEEN...
Any help at all would be greatly appreciated.
What you are describing is one of the reasons you should store datetime data in a datetime column.
Can you post some ddl, sample data and desired output. Take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 8, 2012 at 5:19 pm
Hmm, it's not clear to me what "fields" 2 and 3 contain, so I'll assume field2 has the "4Q" and field3 has "12".
Just in case field1 is indexed, or it could be some day, let's avoid using functions on it (which is good technique anyway):
SELECT *
FROM dbo.tablename
WHERE Field1 >= '20' + field3 + CASE field2
WHEN '1Q' THEN '01'
WHEN '2Q' THEN '04'
WHEN '3Q' THEN '07'
WHEN '4Q' THEN '10' ELSE '01' END + '01' AND
Field1 < DATEADD(QUARTER, 1, '20' + field3 + CASE field2
WHEN '1Q' THEN '01'
WHEN '2Q' THEN '04'
WHEN '3Q' THEN '07'
WHEN '4Q' THEN '10' ELSE '01' END + '01')
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 24, 2012 at 10:32 am
If Field2 and Field3 are qtr/year values then you can re-arrange them to YY'Q'QTR so it's sortable. Next convert Field1 to the same format which will allow you to filter using BETWEEN.
IF object_id('tempdb..#tbl') IS NOT NULL
BEGIN
DROP TABLE #tbl
END
-- Assumptions
-- 1) Field2 <= Field3
-- 2) Valid ranges for Field2 and Field3 are in the expected format and between [2000, 2099]
-- 3) Field1 values can be converted to valid dates between [1/1/2000, 12/31/2099]
-- 4) You are unable to implement a more appropriate design to the table at this time
-- Create some test data
CREATE TABLE #tbl (
Field1 VARCHAR(8),
Field2 VARCHAR(4),
Field3 VARCHAR(4)
)
INSERT INTO #tbl(Field1, Field2, Field3)
VALUES('20120908', '4Q12', '1Q13') -- Field1 is before range
, ('20121108', '4Q12', '1Q13')-- Field1 is in range
, ('20130408', '4Q12', '1Q13')-- Field1 is after range
-- Test select
SELECT *
FROM #tbl
WHERE RIGHT(LEFT(Field1, 4), 2) + 'Q' + CAST((MONTH(CONVERT(DATE, Field1, 112)) - 1) / 3 + 1 AS VARCHAR)
BETWEEN RIGHT(Field2, 2) + 'Q' + LEFT(Field2, 1) AND RIGHT(Field3, 2) + 'Q' + LEFT(Field3, 1)
November 24, 2012 at 6:29 pm
Indeed, you are making a crucial mistake by not using appropriate datatypes.
The penalty you give yourself is tons of problems you will experience now and in the future, and even more time to fix them.
Cheaper and faster is to use the right datatype for all columns. In this case, date.
Field1 string with format YYYYMMDD would become real date, not string.
Field2 and Field3 with format QQYY would also become real date. E.g. 2Q12 would become date 2012-04-01.
It will be easier and cleaner if you create deterministic function to convert from QQYY to date.
If you do not want to change existing fields, alternative is to add 3 new calculated non-persistent fields of datatype DATE,
create indexes on those field(s) and use those calculated fields in your WHERE clause.
It will be much much faster because you have index on that field and optimizer can use it because there is no function around that field.
November 25, 2012 at 11:54 pm
dj1202 (11/8/2012)
Hello everyone, I have 3 fields, all text strings but all supposed to represent dates.Field 1: varchar in the format of '20121108'.
Field 2 and 3: varchar string supposed to represent quarters and years. For example, '4Q12' is used to represent 4th quarter of year 2012.
I'm trying to write a query to check if Field 1 is BETWEEN Field 2 and Field 3.
SELECT *
FROM Table
WHERE CONVERT(date,Field1, 111) BETWEEN...
Any help at all would be greatly appreciated.
You have multiple problems here. First, the "Field 1" date needs to be cast/converted to a proper date datatype at some point. Eventually, and at the worst possible time (Murphy's Law), this is going to fail if the varchar "date" is not a valid one (e.g., '20121131'). And by the way, SQL's ISDATE function has some serious flaws so you need to be careful when using that. Here's a discussion on a similar topic: Get date part from a filename string
So if it's not possible to fix the source data, then I'd recommend writing a query to import the dates into an intermediate staging table that could be validated before being used in a production query. This could be time-consuming depending on where you are getting the data from, how often it changes, etc. But if the data import is an infrequent process relative to the query itself then getting the data validated and into properly typed columns FIRST will avoid inevitable datetime datatype errors and the need to use CAST/CONVERT in the WHERE clause of your production query--which is generally not a good idea.
Then of course you have the problem of interpreting what '4Q12' is so that date logic can be applied to it. If you choose to import to a staging table, your import procedure/query can use some of the ideas posted above by others to put these quarter/year strings into the proper date format according to your particular business rules. If these quarter boundaries are going to be static then you should probably just create a separate table (as noted in the post just above).
Thus, once you have validated and properly typed columns the WHERE date1 BETWEEN date2 and date3 part of the query is easy. (Keep in mind that the BETWEEN clause is INCLUSIVE when setting up your quarter boundaries.)
Of course, you will have to compare the performance issues of importing, validating, and otherwise "cleaning up" and converting the data first against the performance of a query or procedure doing all of this "on the fly." That will depend on how often you do one or the other or both.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply