February 10, 2011 at 1:25 pm
I have a table in which date is divided into 3 columns salesyear,salesmonth and salesday.
Now I need to create a sql select query for a date range say from 1/20/2010 to 2/9/2010.
I am not sure how to do that.Please help!
February 10, 2011 at 1:46 pm
You'll have to post the DDL and sample data (see first link in my sig for help if needed to make it consumable) for us to help you on this. At first glance, you're going to need to do some form of concatonation of the information into real datetime values, blowing up any chance for avoiding a clustered scan. After that's done, you should be able to use standard practices.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 10, 2011 at 1:50 pm
Here is the table Structure:-
CREATE TABLE [dbo].[Test](
[projCode] [nvarchar](5) NULL,
[salesMonth] [int] NULL,
[salesDay] [int] NULL,
[salesYear] [int] NULL,
[decalYear] [int] NULL,
[transactionCount] [bigint] NULL,
[transactionType] [nvarchar](5) NULL,
[ExportDate] [datetime] NULL
) ON [PRIMARY]
AND SAMPLE DATA :-
SELECT '023','1','1','2011','0''0','VR' UNION ALL
SELECT '023','1','2','2011','0''0','VR' UNION ALL
SELECT '023','1','3','2011','0''0','VR' UNION ALL
SELECT '023','1','4','2011','0''40','VR' UNION ALL
SELECT '023','1','5','2011','0''23','VR' UNION ALL
SELECT '023','1','6','2011','0''30','VR' UNION ALL
SELECT '023','1','7','2011','0''19','VR' UNION ALL
SELECT '023','1','8','2011','0''0','VR' UNION ALL
February 10, 2011 at 2:00 pm
Yeah, I was afraid of that. You'll have to do something like this short term:
;with cte AS
( SELECT *, CONVERT( DATETIMe, CONVERT( VARCHAR(20), salesmonth) + '/' + CONVERT( VARCHAR(20), salesday) + '/' + CONVERT( VARCHAR(20), salesYear)) AS salesFullDatetime
FROM
Test
)
SELECT *
FROM cte
WHERE salesFullDateTime BETWEEN @startDate and @endDate
In the long run, you'll want to convert this table to store the dates properly and get an index on it so you can let the optimizer do the majority of the work for you.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 10, 2011 at 3:28 pm
Craig Farrell (2/10/2011)
Yeah, I was afraid of that. You'll have to do something like this short term:
;with cte AS
( SELECT *, CONVERT( DATETIMe, CONVERT( VARCHAR(20), salesmonth) + '/' + CONVERT( VARCHAR(20), salesday) + '/' + CONVERT( VARCHAR(20), salesYear)) AS salesFullDatetime
FROM
Test
)
SELECT *
FROM cte
WHERE salesFullDateTime BETWEEN @startDate and @endDate
In the long run, you'll want to convert this table to store the dates properly and get an index on it so you can let the optimizer do the majority of the work for you.
@Craig: Two things I would change: First, the data type (VARCHAR(20) is not really appropriate in this case) and second the order of month, day and year (add a SET DATEFORMAT dmy or SET LANGUAGE 'GERMAN' before your query and see what happens... ;-))
The revised query would look like
;WITH cte AS
(
SELECT *,
CAST(CONVERT( CHAR(4), salesYear)+ RIGHT(100 + salesmonth,2)+ RIGHT(100 + salesday,2) AS DATETIME) as salesFullDatetime
FROM Test
)
SELECT *
FROM cte
WHERE salesFullDateTime BETWEEN @startDate AND @endDate
I second your recommendation to store the data in a date column. It could even be a computed persisted column added to the current design.
February 10, 2011 at 3:44 pm
LutzM (2/10/2011)
@Craig: Two things I would change: First, the data type (VARCHAR(20) is not really appropriate in this case) and second the order of month, day and year (add a SET DATEFORMAT dmy or SET LANGUAGE 'GERMAN' before your query and see what happens... ;-))
Yeah, I thought of that after, but I figured he'd come back with exploding query complaints if it ended up as a problem. 🙂 You're right, though, I should have addressed it in the first pass.
I used VARCHAR(20) because I was being lazy and couldn't remember if the string would end up concatonated if I used VC(4) for the year and (2) for the month/day during the concatonation conversion.
The revised query would look like
;WITH cte AS(
SELECT *,
CAST(CONVERT( CHAR(4), salesYear)+ RIGHT(100 + salesmonth,2)+ RIGHT(100 + salesday,2) AS DATETIME) as salesFullDatetime
FROM Test
)
SELECT *
FROM cte
WHERE salesFullDateTime BETWEEN @startDate AND @endDate
Hm, YYYYMMDD format? Right right... ISO conversion. Good call. I like the bit with the 100+ instead of replication routines. Nice touch. Kinda surprised at the implicit conversion in the RIGHT() statement, though.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 10, 2011 at 3:52 pm
Craig Farrell (2/10/2011)
...Hm, YYYYMMDD format? Right right... ISO conversion. Good call. I like the bit with the 100+ instead of replication routines. Nice touch. Kinda surprised at the implicit conversion in the RIGHT() statement, though.
Regarding the implicit conversion (straight from BOL):
character_expression can be of any data type, except text or ntext, that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convert character_expression.
But who'd look up RIGHT() in BOL? :hehe:
February 11, 2011 at 7:15 am
Thanks It worked!
February 11, 2011 at 4:45 pm
schauhan13 (2/10/2011)
I have a table in which date is divided into 3 columns salesyear,salesmonth and salesday.Now I need to create a sql select query for a date range say from 1/20/2010 to 2/9/2010.
I am not sure how to do that.Please help!
My recommendation would be to convert the table to have a real DATETIME datatype in it instead of the individual columns. Since you probably can't do that, my second recommendation would be to add a persisted computed column with the following formlua in it and then query that column...
DATEADD(yy,SalesYear-1900,DATEADD(dd,SalesDay-1,DATEADD(mm,SalesMonth-1,0)))
If no mods to the table are allowed, you could build a "sister table" with the correct data. Last and least, you could also build a view with the forumul I posted above.
The formula above is VERY fast because it doesn't have any character based conversions and only uses integer math.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply