• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

union all

Hi,

I am using UNION ALL to create headers so I can copy and paste the result direct to excel file

select 'username' as h1, 'userid' as h2, 'userbdate' as h3
union all
select username,userid, convert(date,userbdate,101)
from usertable


I am getting

Conversion failed when converting date and/or time from character string.


any ideas?  thx
0
mcrmg
Asked:
mcrmg
  • 5
  • 4
1 Solution
 
Dale BurrellDirectorCommented:
There is some invalid data in your table...
0
 
mcrmgAuthor Commented:
if I take out everything from UNION ALL and up, just the data part, it runs fine.  thx
0
 
Dale BurrellDirectorCommented:
You can't mix data types in the same column - which is what you are trying to do with your union, the first select is selecting a string for h3 and the second select is selecting a date.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
mayank_joshiCommented:
select username,userid, convert(date,userbdate,101)
from usertable

Open in new window

if the above query is running fine then following should work:-

select 'username' as h1, 'userid' as h2,  convert(date,'userbdate',101) as h3
union all
select username as h1,userid as h2, convert(date,userbdate,101) as h3
from usertable

Open in new window


0
 
Dale BurrellDirectorCommented:
what do you think "convert(date,'userbdate',101)" will do?
0
 
mayank_joshiCommented:
further, 'userbdate' should be in  “mm/dd/yyyy” format for your query.
0
 
Dale BurrellDirectorCommented:
'userbdate' is his text heading... its not a date... which is the problem. The only way you can accomplish what you are trying to do is to convert the date to a string so you have the same data type. If you choose the correct format for the date as you convert it to a string Excel should turn it back into a date.
0
 
mayank_joshiCommented:
@ dale_burrell:
'userbdate' is taken just for example.

it can be anything like:-

convert(date,'12/30/2010',101)

Open in new window

0
 
Dale BurrellDirectorCommented:
'userbdate' isn't an example though... its the actual data in this question.
0
 
mayank_joshiCommented:
this should do:-

select 'username' as h1, 'userid' as h2,  'userbdate' as h3
union all
select username as h1,userid as h2, convert(varchar(10),userbdate,101) as h3
from usertable

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now