Solved

union all

Posted on 2011-03-25
10
269 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
 
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
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: 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

Suggested Solutions

Title # Comments Views Activity
STDEVP in SQL 2 58
query help 18 57
Oracle - Create Procedure with Paramater 16 57
sql query help 4 45
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
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…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

863 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

25 Experts available now in Live!

Get 1:1 Help Now