August 19, 2003 at 1:31 pm
We are converting a char to numeric (so we can do a sort) and the values being returned are wrong. Most of the time they are fine as in the first example.
Here's the syntax:
This one is fine:
select convert(int,(convert(varbinary(10),rtrim('2003-189807')))) --returns 943274032
select convert(int,(convert(varbinary(10),rtrim('2003-190047')))) --returns 959459380
This one is wacky:
select convert(int,(convert(varbinary(20),rtrim('2003-189807')))) --returns 959983671
select convert(int,(convert(varbinary(20),rtrim('2003-190047')))) --returns 808465463
Why would the bottom one line return a value that's lower (then the first example) , when the number we are passing it is higher?
Any help or suggesions GREATLY appreciated.
August 20, 2003 at 9:19 am
I'm not sure this makes sense - so please forgive my ignorance. Because of the hyphen, there is no direct conversion from the string to a number.
Converting the string first to varbinary yields different results because the original string is 11 characters. Using binary 10 truncates the last character.
What will the number be used for?
Guarddata-
August 20, 2003 at 10:27 am
another solution : convert the date/time into second by reference to a fixed date
example : datediff(ss,@yourdate, getdate())
August 21, 2003 at 9:40 am
You say that you need to SORT and that the original values are CHAR. Assuming that the first four digits are year and that those after the hypen are are incremental with time, a sort on the native CHAR data works just fine. I entered 20 rows of similar data into a CHAR(11) field and sorting & grouping worked out just fine. What am I missing?
August 21, 2003 at 9:52 am
I do believe it should not be necessary to convert to a number to do a sort. String sorting should work fine, except when you are having troubles with leading zeros.
In that case, I would suggest taking out the hyphen and then converting to a numeric value.
convert(int, left(value, 4) + right(value, len(value)-5))
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply