CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Saeid
Saeid

Reputation: 13582

T-SQL select script with union

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?

Upvotes: 3

Views: 529

Answers (3)

Preet Sangha
Preet Sangha

Reputation: 65506

In a union you have one type per column.

Convert(varchar,@Mydate,101) [Date] is being unioned with a column that's a datetime (@Mydate) so it's automatically cast or converted to a datetime.

So ask yourself this.

How is 2011-12-10 16:32:00.800 and 12/10/2011 going to the same type?

Answer: make the first a varchar too but in the long dateime format using Convert(varchar, @myDate, 21)

declare @Mydate datetime
set @Mydate = getdate()

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


Date                           Describe
------------------------------ ---------------
2011-12-11 02:45:44.660        Normal DateTime
12/11/2011                     Format 101

(2 row(s) affected)

Upvotes: 0

sll
sll

Reputation: 62504

Looks like variable @Mydate is of DateTime type and DateTime precedence is higher than Date precedence.

T-SQL UNION (MSDN):

When data types differ, the resulting data type is determined based on the rules for data type precedence.

Data Type Precedence (Transact-SQL)

When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence.

Upvotes: 2

marc_s
marc_s

Reputation: 754538

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!)

Upvotes: 2

Related Questions