SQL Server Performance

convert smalldatetime to varchar and add to other field!

Discussion in 'General Developer Questions' started by Sidd, Sep 19, 2007.

  1. Sidd New Member

    hi friends!
    I am facing a situation where I have to add 4 columns to create uniqueness for a datacomponent .
    I am trying to count ( disctinct a+b+c+d+e)
    now a= smalldatetime . when i remove small date time it is ok . but how i can convert smalldate time to add to other fields can you please suggest.
  2. FrankKalis Moderator

    Can you explain with a small example what you are trying to achieve?
  3. Jack Vamvas Member

    Hi
    Do you mean something like CONVERT(VARCHAR(12),@myDateTime) ? If not , could you clarify the question. i.e are you trying to convert to a VARCHAR ?
  4. Adriaan New Member

    Looks like you're trying to check if you will get a constraint violation when adding a unique constraint on those 5 columns?
    COUNT(DISTINCT a+b+c+d+e) concatenates the strings from your columns. Note that you have to anticipate that column a can have values like 'a' and 'ab' , and column b can have values like 'b' and 'bc'. The way you do it, the result for a+b can be 'a'+'b'='ab', but it can also be 'ab'+''='ab' - so you need to put a delimiter character between the columns, like COUNT(DISTINCT a+'|'+b+'|'+c+'|'+d+'|'+e)
    Next, if any of the columns is nullable, and indeed has nulls, the result of COUNT(DISTINCT a+b) will be at least one less than the actual number of distinct values, because a concatenation with a null yields a null. Before the SELECT statement, you can add SET CONCAT_NULL_YIELDS_NULL OFF as the quickest solution - otherwise you have to add ISNULL()s around the column references.
    Finally, like Jack suggested, you need to use CAST or CONVERT to concatenate the date into the expression.
    ... but in the end, the easiest solution to get the number of distinct values is this:
    SELECT COUNT(*) FROM (SELECT DISTINCT a, b, c, d, e FROM mytable)
  5. Madhivanan Moderator

    Adriaan's query needs alias name fro derived table
    SELECT COUNT(*) FROM (SELECT DISTINCT a, b, c, d, e FROM mytable) as T

Share This Page