Solved

union all

Posted on 2011-03-25
10
272 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SELECT query on two levels (detail and summary) 13 57
get column names from table in vb.net 8 51
2 IIF's in Access query 25 44
Need return values from a stored procedure 8 21
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

830 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