Solved

union all

Posted on 2011-03-25
10
268 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
Comment Utility
There is some invalid data in your table...
0
 

Author Comment

by:mcrmg
Comment Utility
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
Comment Utility
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
 
LVL 9

Expert Comment

by:mayank_joshi
Comment Utility
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
Comment Utility
what do you think "convert(date,'userbdate',101)" will do?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 9

Expert Comment

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

Accepted Solution

by:
Dale Burrell earned 125 total points
Comment Utility
'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
Comment Utility
@ 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
Comment Utility
'userbdate' isn't an example though... its the actual data in this question.
0
 
LVL 9

Expert Comment

by:mayank_joshi
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

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…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now