Please suggest - how to achieve the final table?

ASHMITP 141 Reputation points

Hi there ,
Please assist
I have two table

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

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

What should I do to achieve WL_final ?
Do I need to do union ?

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


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

    If I am using these sample data...

    USE [DemoDB]

    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]

    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,,'null' descp, from dbo.[WL_test] a
    select,,b.descp, from [dbo].[WL_test1] b

    I am getting the result that I would expect:


    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. LiHong-MSFT 10,046 Reputation points

    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,

    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