opc.three (6/29/2011)

---

Itzik has it down, use the APPLY operator for this. UNION ALL will scan the data multiple times, APPLY will only scan it once, and it's cleaner than the...

Here is why UNPIVOT doesn't work with multiple sets of data.

Suppose you have the row:

JOB Name1 Name2 ...

The UNPIVOT operator doesn't work well when you want to unpivot multiple sets of data. Here the OP wants to unpivot three sets of data:...

How is this an improvement over the code listed below that the OP is already using?

select convert(varchar,getdate(),112)

I can't imagine that the efficiency of converting...

This is a variation of the overlapping date ranges problem. ...

This syntax was introduced in SQL 2008. Since you're posting in the SQL 2005 forum and you're getting error messages about...

Actually what you are looking for is IN itself. There's nothing that requires the left side to be a field and nothing that prevents the right side from being...

Both WITH CUBE and WITH ROLLUP require that you have a GROUP BY clause, which I don't think that you want to use in this case. The COMPUTE clause...

One caveat is that in SQL 2008, it automatically takes DST into account, so you'll need to make sure that your UDF also takes that into account.

Drew

This is datetimeoffset data. It was introduced in SQL2008. It combines datetime data with a time zone offset.

Drew

Jeff Moden has previously posted about how inefficient it is to convert datetime data to varchar. His most recent post is here http://www.sqlservercentral.com/Forums/FindPost1119797.aspx.

There is a solution that only uses...

T-SQL does not use the ASCII null character to represent a NULL string. As far as SQL is concerned, there is no difference between ASCII 0 and any other...

Andrew Westgarth (6/21/2011)

---

I didn't need to do this for presentation it was for comparison against a varchar field. Basically I have a user input varchar of time e.g. 02:32:53...

You probably shouldn't be doing this in T-SQL at all. This is a presentation issue and should be left to the presentation layer.

What are you ultimately trying to achieve?...

This sounds suspiciously like a homework assignment. Why don't you tell us your thoughts?

Drew