October 12, 2011 at 7:57 pm
Hi,
I am in need of converting a datetime column into character type and then sort it once it is converted to character. I am not sure which convert style 102 or 120 will be best to use if I want to convert to character and then sort? I am using SQL server 2005 and convert style 102 is ANSi compliant and 120 is ODBC canonical. What happens if I use style 120, i mean do I have to look for any compatibilty issues before using this 120 style?
My sql query I am using is as follow with style 102 and 108 as I also want the date and time when converting to character:
SELECT starttime, convert(varchar(20), starttime,102)+convert(varchar(20), starttime,108) FROM testdate ORDER BY convert(varchar(20), starttime,102)+convert(varchar(20), starttime,108)
October 12, 2011 at 10:00 pm
vick12 (10/12/2011)
Hi,I am in need of converting a datetime column into character type and then sort it once it is converted to character. I am not sure which convert style 102 or 120 will be best to use if I want to convert to character and then sort? I am using SQL server 2005 and convert style 102 is ANSi compliant and 120 is ODBC canonical. What happens if I use style 120, i mean do I have to look for any compatibilty issues before using this 120 style?
My sql query I am using is as follow with style 102 and 108 as I also want the date and time when converting to character:
SELECT starttime, convert(varchar(20), starttime,102)+convert(varchar(20), starttime,108) FROM testdate ORDER BY convert(varchar(20), starttime,102)+convert(varchar(20), starttime,108)
What is wrong with just using ORDER BY starttime?
October 13, 2011 at 2:10 am
Lynn Pettis (10/12/2011)
What is wrong with just using ORDER BY starttime?
I suspect (just a wild guess) that the query contains DISTINCT in the select list and, since the OP is selecting the converted date, can only ORDER BY the converted date.
IMHO, you should *NOT* convert dates in T-SQL and leave this task to the application layer. Passing strings instead of dates to the app side means that the app will have to parse and convert them back to dates in order to perform sorts and comparisons.
It's like using Paint as a word processor and OCR to extract the text from the picture. Do yourself a favour: don't do it.
-- Gianluca Sartori
October 13, 2011 at 2:21 am
I suspect (just a wild guess) that the query contains DISTINCT in the select list and, since the OP is selecting the converted date, can only ORDER BY the converted date.
Given that the OP provided the actual query being executed, this is more than just a wild guess! 😀 I'm backing Lynn.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 13, 2011 at 3:36 am
Phil Parkin (10/13/2011)
I suspect (just a wild guess) that the query contains DISTINCT in the select list and, since the OP is selecting the converted date, can only ORDER BY the converted date.
Given that the OP provided the actual query being executed, this is more than just a wild guess! 😀
Well, we don't know if this is the actual query. It could be a simplified version.
I'm backing Lynn.
Me too.
-- Gianluca Sartori
October 13, 2011 at 4:16 am
Well, we don't know if this is the actual query. It could be a simplified version.
True, of course.
You are very much more accommodating than I would ever be, an excellent asset to the community.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 13, 2011 at 6:19 am
Hi All,
Thanks for your replies. Actually I am using SSRS to develop a report and I need to sort the data based on a sort input parameter the user select from teh drop down list.Say if the user select time, then teh stored procedure has a input parameter called @sort of varchar type which check if teh string"time" tehn in teh order by clause I need to sort by time. If the user selects sasy Name as a sort parameter, tehn @sort value is "Name" and in teh order by clause I need to sort by name.
So that why Iam trying to convert the time to varchar format and tehn sort. I am not passing teh converted datatetime value to teh application. teh sql query I listed earlier was to give an example of teh output of the format in teh select clause.
All I need is a convert in orderby clause to convert datetime to varchar because when I am using datetime itselft in the sort by clause then in SSRS I am getting an error like "cannot read next data row for the data set.conversion failed when converting character string to smalldatetime data type." BUt when Iam converting time varchar to style 102 and 108 the sorting is coming perfect. Is it fine to do it that way?
October 13, 2011 at 6:24 am
vick12 (10/12/2011)
SELECT starttime, convert(varchar(20), starttime,102)+convert(varchar(20), starttime,108) FROM testdate ORDER BY convert(varchar(20), starttime,102)+convert(varchar(20), starttime,108)
I don't see a distinct in the original query.
Sorting on starttime will give the same results as sorting on the converted values.
October 13, 2011 at 6:41 am
vick12 (10/13/2011)
Hi All,Thanks for your replies. Actually I am using SSRS to develop a report and I need to sort the data based on a sort input parameter the user select from teh drop down list.Say if the user select time, then teh stored procedure has a input parameter called @sort of varchar type which check if teh string"time" tehn in teh order by clause I need to sort by time. If the user selects sasy Name as a sort parameter, tehn @sort value is "Name" and in teh order by clause I need to sort by name.
So that why Iam trying to convert the time to varchar format and tehn sort. I am not passing teh converted datatetime value to teh application. teh sql query I listed earlier was to give an example of teh output of the format in teh select clause.
All I need is a convert in orderby clause to convert datetime to varchar because when I am using datetime itselft in the sort by clause then in SSRS I am getting an error like "cannot read next data row for the data set.conversion failed when converting character string to smalldatetime data type." BUt when Iam converting time varchar to style 102 and 108 the sorting is coming perfect. Is it fine to do it that way?
I suggest you start auto-correcting 'teh' to 'the' ...
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
October 13, 2011 at 6:45 am
Lynn Pettis (10/13/2011)
vick12 (10/12/2011)
SELECT starttime, convert(varchar(20), starttime,102)+convert(varchar(20), starttime,108) FROM testdate ORDER BY convert(varchar(20), starttime,102)+convert(varchar(20), starttime,108)
I don't see a distinct in the original query.
Sorting on starttime will give the same results as sorting on the converted values.
Now that I re-read my answer I see that it could be misinterpreted. I quoted your "What is wrong with just using ORDER BY starttime?" because I completely agree with it, but I see that it can be read the other way.
I mean that there's no reason to convert the date in T-SQL: let SSRS do the formatting.
You are getting sortable results just because you're using formats 102 and 108, but what would happen if you used format 109 ?
If you're getting errors in SSRS, probably some of the report fields are defined as string and not as date.
-- Gianluca Sartori
October 13, 2011 at 6:48 am
Yes, In ssrs teh input parameter is a string because i have to pass teh values like "date", "name" to teh stored procedure when the user select what i want to sort on. I did not try with 109, is 108 not 24hr format? what is teh difference between 108 and 109.
Right now i am getting perfect sort order with style 102 and 108. PLease advise if i can keep teh same else i have to make lot of changes to my code.
October 13, 2011 at 6:58 am
vick12 (10/13/2011)
what is teh difference between 108 and 109.
Style 109 is 'mon dd yyyy hh:mi:ss:mmmAM', for instance 'Oct 13 2011 2:57:12:350PM'.
AS you can see, this format cannot be sorted correctly as a string.
-- Gianluca Sartori
October 13, 2011 at 7:08 am
vick12 (10/13/2011)
Hi All,Thanks for your replies. Actually I am using SSRS to develop a report and I need to sort the data based on a sort input parameter the user select from teh drop down list.Say if the user select time, then teh stored procedure has a input parameter called @sort of varchar type which check if teh string"time" tehn in teh order by clause I need to sort by time. If the user selects sasy Name as a sort parameter, tehn @sort value is "Name" and in teh order by clause I need to sort by name.
So that why Iam trying to convert the time to varchar format and tehn sort. I am not passing teh converted datatetime value to teh application. teh sql query I listed earlier was to give an example of teh output of the format in teh select clause.
All I need is a convert in orderby clause to convert datetime to varchar because when I am using datetime itselft in the sort by clause then in SSRS I am getting an error like "cannot read next data row for the data set.conversion failed when converting character string to smalldatetime data type." BUt when Iam converting time varchar to style 102 and 108 the sorting is coming perfect. Is it fine to do it that way?
How about posting your stored procedure, that would help us greatly in helping you.
October 13, 2011 at 7:21 am
The format of my procedure is as follow: if not converting to varchar in orderby then I ma getting error in ssrs 2005
create stored procedure testsorting
( @sort varchar(60)
)
as
BEGIN
SELECT starttime,
name,
productno,
from table testA
orderby
CASE
WHEN @SortColumn = 'starttime' THEN (Convert(varchar(20),starttime,102)+ Convert(varchar(20),startime,108))
WHEN @SortColumn = 'productNo' THEN CONVERT(VARCHAR(10), productno)
WHEN @SortColumn = 'Name' THEN CONVERT(VARCHAR(60), name)
END
END
October 13, 2011 at 7:39 am
How about something like this:
create procedure testsorting
( @sort varchar(60)
)
as
BEGIN
IF @sort = 'starttime'
BEGIN
SELECT
starttime,
name,
productno,
from
testA
order by
starttime;
END
ELSE IF @sort = 'productNo'
BEGIN
SELECT
starttime,
name,
productno,
from
testA
order by
productno;
END
ELSE IF @sort = 'Name'
BEGIN
SELECT
starttime,
name,
productno,
from
testA
order by
name;
END
--ELSE
-- You could put an error catch routine here
END;
Another way to go would be to use a master/child set of procedures where a master stored procedure would call the appropriate child procedure based on the input value.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply