Union with different data types ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Union with different data types ?

Author

Topic

JAdauto
Posting Yak Master

160 Posts

Posted - 2013-03-26 : 22:29:31

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

160 Posts

Posted - 2013-03-26 : 22:46:28

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-27 : 00:52:45

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources