T-SQL select script with union

Question

this is my scripts

Select @Mydate [Date], 'Normal DateTime' [Describe]
Select Convert(varchar,@Mydate,101) [Date],'Format 101' [Describe]

and result is:

  Date                            Describe
  2011-12-10 16:32:00.800         Normal DateTime

  Date                            Describe
  12/10/2011                      Format 101

but when I union this 2 scripts the result is different:

Select @Mydate [Date], 'Normal DateTime' [Describe]
Union
Select Convert(varchar,@Mydate,101) [Date],'Format 101' [Describe]


  Date                            Describe
  2011-12-10 16:32:00.800         Normal DateTime
  2011-12-10 00:00:00.000         Format 101

I think the SQL try to fix the number of characters in both raw, so how can I use union and get the previous results?

Answer 1

Try this instead:

Select CONVERT(VARCHAR(50), @Mydate, 121) [Date], 'Normal DateTime' [Describe]
Union
Select CONVERT(VARCHAR, @Mydate, 101) [Date],'Format 101' [Describe]

Gives me an output of:

Date                      Describe
2011-12-10 16:32:00.000   Normal DateTime
12/10/2011                Format 101

When you cast both values to VARCHAR explicitly, they'll show up correctly (I hope!)