January 23, 2009 at 8:42 pm
My apprecaition is great to all that take time to read and give input
Help.... if i can get some help on the below script it would be much appreciated. Also if there is a faster way to run this same thing witn same results on a table with million plus rows. Thanks
my query below: Works but return value is not right
SELECT DISTINCT
ORDER_NUM, CAST(DATEPART(yyyy, OPERATION_DT) AS char) + '-' + CAST(DATEPART(m, OPERATION_DT) AS char) + '-' + CAST(DATEPART(dd,
OPERATION_DT) AS char) AS Expr3
FROM (SELECT TOP 100 *
FROM ClosedOrders_AT WITH (nolock)
ORDER BY OPERATION_DT DESC) DERIVEDTBL
returns something to this manner
order_num {This is one massive field and i want it smaller}
1-1577356376 2009 1 23
i am going for this
order_num date
1-1577356376 2009-1-23
Thanks and hopefully i gave enough info.....
Darryl
DHeath
January 23, 2009 at 8:51 pm
Take a look at "Cast and Convert" in Books Online. It has the data on how to get formatted dates out of the system more easily than that.
On an immediate basis, if you convert the dateparts to "varchar(10)", instead of "char", you'll get more of what you want.
But do look at the default formats available per BOL. One conversion with a preset format will be faster than three conversions and a string operation.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 23, 2009 at 10:17 pm
Thanks for the cast input as it works wonderfully. But i have seen to come across another problem as well which i am sure is due to the fact that the table has 13 million rows and some.
SELECT DISTINCT ORDER_NUM, CAST(OPERATION_DT AS varchar(11)) AS [Date]
FROM (SELECT TOP 100 *
FROM ClosedOrders_AT WITH (nolock)
ORDER BY OPERATION_DT DESC) DERIVEDTBL
The code above appears to work as long as i dont add anymore fields then the 2 you see above (order_num and date) the moment i try adding any more i consistently get "timeouts"
SELECT DISTINCT ORDER_NUM, FIELD_NAME, NAME, NEW_VAL, OLD_VAL, CAST(OPERATION_DT AS varchar(11)) AS [Date]
FROM (SELECT TOP 100 *
FROM ClosedOrders_AT WITH (nolock)
ORDER BY OPERATION_DT DESC) DERIVEDTBL
The table has a total 6 columns any ideas to get them all to display when this runs? without the time out
Thanks again....
DHeath
January 26, 2009 at 8:08 am
Please post the execution plan for the query. Then we can really help out.
If you aren't sure how to do that:
In management studio, on the toolbar, there's a button for Estimate Execution Plan (since your query won't complete, you may not be able to get an Actual Execution Plan, so let's try for the estimate one for now). Click on that.
The execution plan will show up. Right-click it, and select Select Execution Plan As..., save it.
Zip it up and upload it as an attachment to the forum. (Edit Attachments button at the bottom of the page when you're posting a message here.)
Most likely, you're missing indexes that would make a big difference in your query.
Something that might help right off the bat is, only include the columns you actually need in the derived table. Just the ones that you'll use in your out query, instead of "select *".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 26, 2009 at 6:11 pm
Thanks for the replies so far.. much appreciated. After looking closer into the table i did make the necessary changes that were mentioned...adding a useful index as well as when i pull the query take out the rows that are not needed and extended the timeout to zero for now. So i dont think the execution plan is still needed. So thanks a TON for the suggestions...
Now i have run into another situation...which is the query that i posted is kicking my #$%$ when it comes to casting what i need in my date/time field
SELECT DISTINCT
ORDER_NUM, NEW_VAL,OLD_VAL CAST(DATEPART(yyyy, OPERATION_DT) AS varchar(10)) + '-' + CAST(DATEPART(m, OPERATION_DT) AS varchar(10)) + '-' + CAST(DATEPART(dd,
OPERATION_DT) AS varchar(10)) AS Expr3
FROM (SELECT TOP 100 *
FROM ClosedOrders_AT WITH (nolock)
ORDER BY OPERATION_DT DESC) DERIVEDTBL
my returned date values are like this:
2009-10-23 but what i need is this 2009-10-23 14:35:05. And the field (operation_dt is stored down to the millisecond)
as my goal is to make the query pull only the last entry for the day ...meaning if many entries in one day then i grab the very last one closest to 11:59pm. That is why the select distinct is in order here.
Thanks again for all they eyes and any input or suggestions. 😀
DHeath
January 27, 2009 at 7:14 am
Instead of all of this:
CAST(DATEPART(yyyy, OPERATION_DT) AS varchar(10)) + '-' + CAST(DATEPART(m, OPERATION_DT) AS varchar(10)) + '-' + CAST(DATEPART(dd,
OPERATION_DT) AS varchar(10))
Try:
convert(varchar(50), OPERATION_DT, 121)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 27, 2009 at 7:45 am
Hey G 🙂
YOU ROCK!!!!!!!!!!! Thanks a ton for you input and super highly appreciated. The convert was spot on and wooo hooo cant say thanks enough. :w00t:
-Darryl
DHeath
January 27, 2009 at 7:47 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply