SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Union with different data types ?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JAdauto
Posting Yak Master

USA
150 Posts

Posted - 03/26/2013 :  22:29:31  Show Profile  Reply with Quote
I need to return one recordset of data. The fields would need to be such as:

DateOfBusiness
StoreName
StoreID
NetSalesTotals
CheckCount

The thing that makes this complicated, is that I also need to output the column headers as Row 1. So, I declare all the fields in my temp table as varchar(50) and add a SortBit field (0 for my header, 1 for my data). When inserting the actual data into my temp table for rows 2 through x, I convert the DateOfBusiness to varchar as to match my header row.

But now I want the DAteOfBusiness output to be datetime format/data type.

I have tried to do a Union

Select * from #TempTable
Where sortbit = 0
UNION
Select SortBit,
Convert(datetime,DateOfBusiness,110) as DateOfBusiness,
Storename,
StoreID,
NetSalesTotals,
CheckCount
From #TempTable
Where sortBit = 1

However, Im getting "Conversion failed when converting datetime from character string".Im guessing the two different data types for DOB are causing an issue, but maybe it is my convert of the data.

Thoughts? Ideas? Assistance? Surely someone has had to output data for a row of headers, PLUS also have the data in the right data type format. ???

Thx

JAdauto
Posting Yak Master

USA
150 Posts

Posted - 03/26/2013 :  22:46:28  Show Profile  Reply with Quote
I should add that I realize I dont normally need a Union on two selects from teh same table... I was just trying it in order to capture the results in two different formats in one resultset. I want to see

DateOfbusiness StoreName StoreID NetSalesTotals checkCount
03-26-2013 ABC Store 1001 1287.65 349
03-26-2013 XYZ Store 1009 845.32 197

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 03/27/2013 :  00:52:45  Show Profile  Reply with Quote
Select SortBit,
CASE WHEN DateOfBusiness != 'YourHeaderName( may be DateOfbusiness )' THEN Convert(datetime,DateOfBusiness,110) END as DateOfBusiness,
Storename,
StoreID,
NetSalesTotals,
CheckCount
From #TempTable
Where sortBit IN (0, 1)
ORDER BY sortBit ASC

Edited by - bandi on 03/27/2013 00:56:02
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000