You must Sign In to post a response.
  • Category: .NET

    In SQL SERVER how to split one Column's data into another table with 2 column

    I have question on how to split one Column's data into another table
    with 2 column. in SQL SERVER
    E.G.
    Original TEXT Field Column
    08/01/2003 14:23:52.125

    New Table
    Column 1 Column 2
    ---------- -----------
    01/08/2003 14:23:52

    Column 1 is in (DD/MM/YYYY)
    Column 2 is in (hh:mm:ss)
  • #498372
    Hi Sakthi,

    try this example,

    create database TESTDB

    USE [Testdb]
    GO
    /****** Object: Table [dbo].[table1] Script Date: 04/26/2010 14:16:58 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[table1](
    [date] [datetime] NULL
    ) ON [PRIMARY]


    USE [Testdb]
    GO
    /****** Object: Table [dbo].[table1] Script Date: 04/26/2010 14:16:58 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TABLE [dbo].[table2](
    [date2] [datetime] NULL,
    [time2] [nchar](10) COLLATE Turkish_CI_AS NULL
    ) ON [PRIMARY]


    select * from table1
    select * from table2
    insert into table1 values(getdate())
    insert into table2(date2,time2) select CONVERT(CHAR(10),date,102),CONVERT(CHAR(10),date,8) from table1

  • #498396
    Hi Sakthi Ram,

    please execute first three lines in sql server you get the answer
    Example 1:

    declare @data datetime

    set @data= '11/25/2009 10:59:43 AM'
    select Convert(varchar,@data,101) as Date,convert(varchar,@data,108)as Time

    output:
    Date Time
    --------- --------
    11/25/2009 10:59:43



    example 2 :To insert into table directly
    insert into secondtable(date,time) select convert(varchar,@datetimefield,101),convert(varchar,@datetimefield,108) from firsttable

    If helpfull give me points
    Thanks & Regards
    Vardhan


  • This thread is locked for new responses. Please post your comments and questions as a separate thread.
    If required, refer to the URL of this page in your new post.