October 26, 2005 at 6:21 am
I inherited a table whereby the date is broken out into three separate fields (numeric) instead of using the easier sql server datetime datatype. The name of the fields are dy, mn,yr and are 2 numeric digits each. Is there a way to query for date range using the scenario above using the between clause.
Here is what the query looks like now:
select mission.mid,flt.dy,flt.mn,flt.yr,flt.taskingcomments,flt.generalcomments
from flt, mission
where flt.mseq=mission.mseq
and flt.patrolhours <=2
and flt.yr=93 (replace this line with a between statement)
However, I would like to replace the last part of the where clause with a between statement based on the fields referred to above. Is this possible? If so, please give sample code. Any help would be greatly appreciated.
October 26, 2005 at 6:38 am
There are couple of options - one is to create a computed column that will compute and store it as a datetime and then you can index it and your queries can be based off that column - for example:
CREATE TABLE TESTCASE
(
ROW_NUM INT IDENTITY(1,1),
DD INT,
MM INT,
YR INT,
COL4 AS CAST((CASE WHEN YR > 49 THEN '19'
ELSE '20'
END) + (CASE WHEN LEN(YR) = 1 THEN '0' + CAST(YR AS VARCHAR(2))
ELSE CAST(YR AS VARCHAR(2))
END)
+ '-' +
(CASE WHEN LEN(MM) = 1 THEN '0' + CAST(MM AS VARCHAR(2))
ELSE CAST(MM AS VARCHAR(2))
END)
+ '-' +
(CASE WHEN LEN(DD) = 1 THEN '0' + CAST(DD AS VARCHAR(2))
ELSE CAST(DD AS VARCHAR(2))
END) AS DATETIME)
)
GO
INSERT INTO TESTCASE (DD, MM, YR) VALUES (12, 12, 5)
INSERT INTO TESTCASE (DD, MM, YR) VALUES (10, 11, 4)
INSERT INTO TESTCASE (DD, MM, YR) VALUES (19, 12, 4)
INSERT INTO TESTCASE (DD, MM, YR) VALUES (1, 5, 5)
INSERT INTO TESTCASE (DD, MM, YR) VALUES (10, 3, 47)
INSERT INTO TESTCASE (DD, MM, YR) VALUES (10, 3, 57)
GO
SELECT * FROM TESTCASE
--Output
ROW_NUM DD MM YR COL4
----------- ----------- ----------- ----------- ------------------------------------------------------
1 12 12 5 2005-12-12 00:00:00.000
2 10 11 4 2004-11-10 00:00:00.000
3 19 12 4 2004-12-19 00:00:00.000
4 1 5 5 2005-05-01 00:00:00.000
5 10 3 47 2047-03-10 00:00:00.000
6 10 3 57 1957-03-10 00:00:00.000
(6 row(s) affected)
You can have your own custom logic in the computed column that forms the datetime logic...I have treated anything that is more than 49 to be one that belonged to the last century i.e. 50 will be treated as 1950...anything less will be treated as of this century i.e. 10 will be treated as 2010.
Hth.
October 26, 2005 at 7:12 am
And better create index on this computed column. Clustered, if possible.
Clustered index is the best option for selecting range of data.
_____________
Code for TallyGenerator
October 26, 2005 at 7:55 am
Another quick question, do I move all other relevant data (that returned by a select), over to the new table(TESTCASE) if I want to return it as well as the datetime data?
October 26, 2005 at 7:59 am
No, the testcase was just toshow you how to make it work in general. The solution must be adapted to your tables.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply