Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

union all

Posted on 2011-03-25
10
Medium Priority
?
277 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
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 500 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

704 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