question

ASHMITP-0361 avatar image
0 Votes"
ASHMITP-0361 asked LiHongMSFT-3908 answered

Please suggest - how to achieve the final table?

Hi there ,
Please assist
I have two table

WL WL_1
C1 C2 C3 C1 C2 C4
1 2 3 11 22 33

I need to achieve-
WL_final
C1 C2 C4 C3
11 22 33
1 2 3

What should I do to achieve WL_final ?
Do I need to do union ?
198813-goal.png


when doing union -
I got this error msg
Conversion failed when converting date and/or time from character string.

198814-image.png


sql-server-generalsql-server-transact-sql
goal.png (4.4 KiB)
image.png (5.0 KiB)
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I have two table

I don't see any table, only a wild character mix.

That's to less on information.
Please post table design as DDL, some sample data as DML statement and the expected result.
1 Vote 1 ·

CREATE TABLE [dbo].[WL_test](
[ID] [int] NULL,
[name] [nvarchar](50) NULL,
[date] [datetime] NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[WL_test1](
[ID] [int] NULL,
[name] [nvarchar](50) NULL,
[descp] [nvarchar](50) NULL,
[date] [datetime] NULL
) ON [PRIMARY]


I tried using Union - it worked with sample data
select a.ID,a.name,'null' descp,a.date from [TEST].dbo.[WL_test] a
Union
select B.id,B.name,b.descp,b.date
from
TEST.[dbo].[WL_test1] b


but failing original is failing


198767-image.png



please check the image goal.png

0 Votes 0 ·
image.png (5.0 KiB)

Which sample data?
As Olaf already said above... we might need some sample data from you

In your first post, you wrote 1 / 11 in a DateTime column which won't work.

0 Votes 0 ·

In your query you address the database TEST; is the tabe design from that DB? By the error message I guess not.
In a UNION query the data type of the columns must match in each sub query.

0 Votes 0 ·

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

0 Votes 0 ·
BjoernPeters avatar image
0 Votes"
BjoernPeters answered BjoernPeters published

If I am using these sample data...

USE [DemoDB]
GO

INSERT INTO [dbo].[WL_test] ([ID],[name],[date]) VALUES (1,1,'2022-05-05 01:00:00.000');
INSERT INTO [dbo].[WL_test] ([ID],[name],[date]) VALUES (2,2,'2022-05-05 02:00:00.000');
INSERT INTO [dbo].[WL_test] ([ID],[name],[date]) VALUES (3,3,'2022-05-05 03:00:00.000');
INSERT INTO [dbo].[WL_test] ([ID],[name],[date]) VALUES (4,4,'2022-05-05 04:00:00.000');
INSERT INTO [dbo].[WL_test] ([ID],[name],[date]) VALUES (5,5,'2022-05-05 05:00:00.000');
INSERT INTO [dbo].[WL_test] ([ID],[name],[date]) VALUES (6,6,'2022-05-05 06:00:00.000');
INSERT INTO [dbo].[WL_test] ([ID],[name],[date]) VALUES (7,7,'2022-05-05 07:00:00.000');
INSERT INTO [dbo].[WL_test] ([ID],[name],[date]) VALUES (8,8,'2022-05-05 08:00:00.000');
INSERT INTO [dbo].[WL_test] ([ID],[name],[date]) VALUES (9,9,'2022-05-05 09:00:00.000');
INSERT INTO [dbo].[WL_test] ([ID],[name],[date]) VALUES (10,10,'2022-05-05 10:00:00.000');

USE [DemoDB]
GO

INSERT INTO [dbo].[WL_test1] ([ID],[name],[date]) VALUES (11,11,'2022-05-05 11:00:00.000');
INSERT INTO [dbo].[WL_test1] ([ID],[name],[date]) VALUES (12,12,'2022-05-05 12:00:00.000');
INSERT INTO [dbo].[WL_test1] ([ID],[name],[date]) VALUES (13,13,'2022-05-05 13:00:00.000');
INSERT INTO [dbo].[WL_test1] ([ID],[name],[date]) VALUES (14,14,'2022-05-05 14:00:00.000');
INSERT INTO [dbo].[WL_test1] ([ID],[name],[date]) VALUES (15,15,'2022-05-05 15:00:00.000');
INSERT INTO [dbo].[WL_test1] ([ID],[name],[date]) VALUES (16,16,'2022-05-05 16:00:00.000');
INSERT INTO [dbo].[WL_test1] ([ID],[name],[date]) VALUES (17,17,'2022-05-05 17:00:00.000');
INSERT INTO [dbo].[WL_test1] ([ID],[name],[date]) VALUES (18,18,'2022-05-05 18:00:00.000');
INSERT INTO [dbo].[WL_test1] ([ID],[name],[date]) VALUES (19,19,'2022-05-05 19:00:00.000');
INSERT INTO [dbo].[WL_test1] ([ID],[name],[date]) VALUES (20,20,'2022-05-05 20:00:00.000');


and your statement

select a.ID,a.name,'null' descp,a.date from dbo.[WL_test] a
Union
select B.id,B.name,b.descp,b.date from [dbo].[WL_test1] b

I am getting the result that I would expect:

198770-image.png



image.png (33.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LiHongMSFT-3908 avatar image
0 Votes"
LiHongMSFT-3908 answered

Hi @ASHMITP-0361
With a union query, same columns must be same datatype (or convertible to the same type).
Are you sure that the date columns of these two tables are both datetime data types, and no weird dates like 2022-2-31 in the sample datas?
First, you need to check if all values of date column can be converted to datetime data type normally by using this query: select cast(date as datetime) from #WL_test

In addition,you could have a check on the results of the settings of SET DATEFORMAT and SET LANGUAGE.Refer to this document: ISDATE (Transact-SQL)

Best regards,
LiHong


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.