We help IT Professionals succeed at work.

selecting data range from view

i created a view that holds a create date column.

on the designer query, i format the create date column to:

 CONVERT(varchar(10), CreatedDT, 101) AS CreatedDT


when I query the view using a date range, the range is not respected and I get results outside of the range.

I wonder if anyone could have a clue of where my error may reside.

I am attaching the code.

Thank you.


 the results from the second query include rows from year 2003
Query that makes the view (LabelDetailSummary):

SELECT     Order_ID, Bndl_Group, Part_Desc, Line_No, SUM(TubeFootage) AS BundleFootage, SUM(TubeWeight) AS BundleWeight, Heat_No, Bndl_Pc_Cnt, Length, Inches, 
                      Fraction, BoxDescription, BoxLength_Feet, BoxLength_Inch, BoxWidth_Feet, BoxWidth_Inch, BoxHeight_Feet, BoxHeight_Inch, BoxWeight, Cust_PO, No_of_Bndls, 
                      Box_Nbr, BoxLength, BoxWidth, BoxHeight, CONVERT(varchar(10), CreatedDT, 101) AS CreatedDT, Label_ID
FROM         Label
GROUP BY Order_ID, Bndl_Group, Part_Desc, Line_No, Heat_No, Bndl_Pc_Cnt, Length, Inches, Fraction, BoxDescription, BoxLength_Feet, BoxLength_Inch, BoxWidth_Feet, 
                      BoxWidth_Inch, BoxHeight_Feet, BoxHeight_Inch, BoxWeight, Cust_PO, No_of_Bndls, Box_Nbr, BoxLength, BoxWidth, BoxHeight, CreatedDT, Label_ID


query to select date range from view:

select order_id,  createdDT
from RG_LabelDetailSummary
where  createdDT between '10/19/2011' and '10/20/2011'
order by order_id, createdDT

Open in new window

Comment
Watch Question

Commented:
That's because you are converting your date to a varchar.  Two options to fix. Either convert after selecting from the view and leave the date as a date.  Or you'll have to cast the varchar in the where clause to a date.


Author

Commented:
I can leave the createdDT column on the view as a date, that is ok.

Then on the SELECT query, how would I choose only the date portion, on the format mm/dd/yyyy?

thanks.
Commented:
You could use the same code you were using in the view to get the right format in the select.


select order_id,  CONVERT(varchar(10),createdDT,101) createdDT
from RG_LabelDetailSummary
where  createdDT between '10/19/2011' and '10/20/2011'
order by order_id, createdDT

Author

Commented:
I think it is almost working. the image with results illustrates what I am getting now with the query attached.

If you notice, the date 09/30/2010 is the oldest date of creation for that order, how can I make that date show first? I tried ordering DESC, ASC but neither worked.

select order_id,  CONVERT(varchar(10),createdDT,101) createdDT, line_no 
from RG_LabelDetailSummary1 
where  order_id = 'C233352'
AND createdDT between '09/01/2010' and '09/01/2011' 
ORDER BY createdDT

Open in new window

Capture.PNG
Commented:
Try casting the order by as a datetime

ORDER BY
   CAST(createdDT AS DateTime)

Author

Commented:
Thank you Tim!
CERTIFIED EXPERT
Top Expert 2012

Commented:
In order to take advantage of any index on createdDT all you have to do is either:
Change:
ORDER BY createdDT
To:
ORDER BY 2

Or alias the table as follows (fix the obvious typo in xSELECT):
xSELECT  a.order_id,
        CONVERT(varchar(10), a.createdDT, 101) createdDT,
        a.line_no
FROM    RG_LabelDetailSummary1 a
WHERE   a.order_id = 'C233352'
        AND a.createdDT BETWEEN '20100901' AND '20110901'
ORDER BY a.createdDT

Open in new window

CERTIFIED EXPERT
Top Expert 2012

Commented:
On second thoughts scratch first solution is no different than ORDER BY createdDT, so the second approach is the way to go.

Commented:
acperkins,

Just curious if you could explain why SQL will use the datetime value if an alias is used and the varchar value if it's not?  
CERTIFIED EXPERT
Top Expert 2012

Commented:
The following means order by the alias in other words "CONVERT(varchar(10), a.createdDT, 101)":
ORDER BY createDT

And this means order by the actual column:
ORDER BY a.createdDT


But an example is worth 1000 words:
DECLARE @Temp TABLE (createdDT datetime)

SET NOCOUNT ON
INSERT @Temp (createdDT) 
VALUES ('20111031'), ('20101101')

SELECT CONVERT(varchar(10), createdDT, 101) createdDT
FROM @Temp
ORDER BY createdDT

SELECT CONVERT(varchar(10), t.createdDT, 101) createdDT
FROM @Temp t
ORDER BY t.createdDT

Open in new window

CERTIFIED EXPERT
Top Expert 2012

Commented:
Another way to solve the question is use a different alias, that way there is no ambiguity and the column will be used, as in:
SELECT  order_id,
        CONVERT(varchar(10), createdDT, 101) createdDate,
        line_no
FROM    RG_LabelDetailSummary1
WHERE   order_id = 'C233352'
        AND createdDT BETWEEN '20100901' AND '20110901'
ORDER BY createdDT

Explore More ContentExplore courses, solutions, and other research materials related to this topic.