Please suggest - how to achieve the final table?

ASHMITP 141 Reputation points
2022-05-04T10:23:44.277+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,281 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
{count} votes

Accepted answer
  1. Bjoern Peters 8,856 Reputation points
    2022-05-04T12:16:35.35+00:00

    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

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. LiHong-MSFT 10,046 Reputation points
    2022-05-05T03:00:56.327+00:00

    Hi @ASHMITP
    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.

    0 comments No comments