Solved

union all

Posted on 2011-03-25
10
271 Views
Last Modified: 2012-05-11
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
Comment
Question by:mcrmg
  • 5
  • 4
10 Comments
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 35213707
There is some invalid data in your table...
0
 

Author Comment

by:mcrmg
ID: 35213760
if I take out everything from UNION ALL and up, just the data part, it runs fine.  thx
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 35213782
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35213795
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
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 35213805
what do you think "convert(date,'userbdate',101)" will do?
0
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35213814
further, 'userbdate' should be in  “mm/dd/yyyy” format for your query.
0
 
LVL 21

Accepted Solution

by:
Dale Burrell earned 125 total points
ID: 35213824
'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
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35213829
@ dale_burrell:
'userbdate' is taken just for example.

it can be anything like:-

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

Open in new window

0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 35213832
'userbdate' isn't an example though... its the actual data in this question.
0
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35213850
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

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question