|
|
|
You can't be creating a report with millions of lines.
So you are actually creating something else. Instead of getting those millions of lines and then processing them you should write a stored procedure that gets only the data you need.
|
|
|
|
|
Hi,
When i execute the query
select convert(varchar(2),datepart(day,'2011-11-06'))
the result i get is 6, i need to get the answer as 06 if only one digit, if 2 then no need of 0 in the first place.
How to get that?
Regards,
YPKI
|
|
|
|
|
You can do something like below.
DECLARE @Input as varchar(10) = '2011-11-06'
select case when datepart(day,@Input) <= 9 then
cast('0' + cast(datepart(day,@Input) as varchar(1)) as varchar(2))
else cast(datepart(day,@Input) as varchar(2)) end AS NewDate
Regards
|
|
|
|
|
If you have always same format of datetime yyyy-MM-dd then you can use substring
e.g select substring('2011-11-06',9,2)
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
If they are using a date column, I think this would be better
select substring(Cast(@mydate as varchar(10)),9,2)
|
|
|
|
|
How about: (corrected... thanks Luc)
select right('0' + convert(varchar(2),datepart(day,'2011-11-06')), 2)
modified on Tuesday, June 7, 2011 3:49 PM
|
|
|
|
|
left?
Luc Pattyn [My Articles] Nil Volentibus Arduum
The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Please use <PRE> tags for code snippets, they improve readability. CP Vanity has been updated to V2.3 modified on Friday, June 10, 2011 8:41 PM
|
|
|
|
|
Good catch... fixed. It DID produce the correct answer, but that was happenstance, not design.
|
|
|
|
|
gr8...exactly what i required..
thankyou
|
|
|
|
|
Good call  Have my 5.
Regards
|
|
|
|
|
I suspect that you are trying to solve a problem which you have not stated.
You asked about the solution that you think will solve the problem but if you stated the actual problem a better solution might exist.
|
|
|
|
|
SELECT CONVERT(VARCHAR(10), GETDATE(), 105)</pre>
Place your date inplace of Gatedate()
|
|
|
|
|
I have a table, say for sales data based on months. Two columns, SALES is typed money and YEARMONTH is typed char(6). I want to build a query for two sums: the sales before 201012 and sales after 201101. My difficulty is in composing conditions on char() data. I know that "=" works. Does ">" or "<" work?
Best,
Jun
|
|
|
|
|
What you did so far for writing query which you need?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
You could convert to integer then use > <
|
|
|
|
|
Try this:
select (sum(sales) from myTable where yearmonth <= '201012') as Sales1, (Select sum(sales) from myTable where yearmonth >= '201101') as Sales2
|
|
|
|
|
Jun Du wrote: Does ">" or "<" work?
yes, all operators work on strings that represent numbers, provided the number of characters is constant (i.e. leading zeroes are used when fewer significant digits would be present). Under these conditions alphabetical and numerical sort order is the same [ADDED] for positive integer numbers [/ADDED].
and yes, date sorting chronologically and numerically works the same provided your date, time or datetime is structured from most significant to least significant, so year first, then month, then day, then hour.
therefore, when having YYYYMM (with leading zeroes in MM!), all sort orders are the same (alpha, numeric, chrono).
Nevertheless, it is always wise to store date, time, datetime information in an appropriate type, not a string.
Luc Pattyn [My Articles] Nil Volentibus Arduum
The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Please use <PRE> tags for code snippets, they improve readability. CP Vanity has been updated to V2.3 modified on Sunday, June 5, 2011 1:03 PM
|
|
|
|
|
You are right about storing the date/time info in the appropriate format: the cases when it makes sense to store it differently are few and far between. The only exception to this rule that I've seen was when we needed to select across multiple dates (e.g. first ten minutes of each hour between 9 and 16). Indexing on the hour and minute components, stored in separate two-character fields, helped us avoid a full-table scan.
|
|
|
|
|
Any reason you didnt make the hour and minute columns numeric, say tinyint? Hours and minutes are, after all, numeric.
|
|
|
|
|
I'd prefer tinyints too, but it was our client's table. We were providing a solution to monitor, filter, and visualize the data for them.
|
|
|
|
|
Hi,
Just trying to get some test data out of one our DB - We have a table that has a Typeid (int) column and a details (nvarchar(1000)) column
What I'd like to do is get the top 1 details for each typeid (there are about 40 distinct typeid) so something along the lines of
select top 1 typeid,details from auditlog group by typeid,details,
But where the query returns the top 1 details column for each eventtypeid
like
Typid details
1 details1
2 details2
....
40 details40
Does that make sense?
|
|
|
|
|
Use distinct
Select distinct typeid,details from auditlog order by typeid
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.cacttus.com
|
|
|
|
|
Thanks for the reply.
I don't think I made it clear in my original post so apologies, but the details column could be anything (it's actually an XML fragment) so distinct will bring back all rows anyway or at least more than 1 row per eventid.
I think I'm after a sort of "top 1 distinct" combination.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
