Post reply

Date Query Format Problem

  • September 1, 2012 at 3:23 pm

    #1532709

    This is slow 🙁

    UPDATE ProdDataTB SET StartDate_Time = ''

    CREATE TABLE TestTable (Test DATETIME)

    insert into TestTable (Test)

    select CONVERT(datetime, StartDD+'-'+StartMM+'-'+StartYYYY+' '+StartHH+':'+StartMM+':'+StartSS) from ProdDataTB T;

  • September 1, 2012 at 3:24 pm

    #1532710

    infact it doenst work

  • September 1, 2012 at 3:45 pm

    #1532711

    UPDATE ProdDataTB SET StartDate_Time = ''

    --CREATE TABLE TestTable (Test NVARCHAR(100))

    insert into dbo.TestTable (Test)

    select StartDD+'-'+StartMM+'-'+StartYYYY+' '+StartHH+':'+StartMM+':'+StartSS from ProdDataTB T;

    Select convert(datetime,[Test],103) as [Date] from TestTable

    looking better but how do I insert the result into another table

  • September 1, 2012 at 4:27 pm

    #1532712

    Still failing

    UPDATE ProdDataTB SET StartDate_Time = ''

    --CREATE TABLE TestTable (Test NVARCHAR(100))

    insert into dbo.TestTable (Test)

    select StartDD+'-'+StartMM+'-'+StartYYYY+' '+StartHH+':'+StartMM+':'+StartSS from ProdDataTB T;

    insert into dbo.ProdDataTB (StartDate_Time)

    select convert(datetime,[Test],103) as [Date] from TestTable

    so slow

  • September 2, 2012 at 7:30 am

    #1532723

    jerome.morris (9/1/2012)

    The table can look like anything really, the rest I can play with. Stagging table sounds good, I just want to remove everything I don't need after but for now I would like to know how to join columns in a way that I can insert into date time column. Sorry if my questions are simple but I am really new to SQL and my project moves faster than my knowledge.

    Jay

    Your questions really aren't simple because I don't have enough information on what you're trying to do. You know what's happening but we don't.

    This is what I think you have, so far...

    The table you posted is a staging table and you use it to import data from a file that looks similar to the table. Is that correct or not? Either way, let us know.

    Actually, I recommend that you start over and tell us everything so we can help solve this problem.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 2, 2012 at 7:58 am

    #1532727

    Hi your correct, I want to import a cvs to nvarchar columns then export the mentioned above to another table converting to a date time in the process

    Jay

  • September 2, 2012 at 9:23 am

    #1532736

    jerome.morris (9/1/2012)

    It gets better look at the create for the table

    USE [SLADB]

    GO

    /****** Object: Table [dbo].[ProdDataTB] Script Date: 09/01/2012 12:27:41 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[ProdDataTB](

    [MachineName] [nvarchar](10) NULL,

    [ModeName] [nvarchar](10) NULL,

    [FileName] [nvarchar](10) NULL,

    [JobName] [nvarchar](10) NULL,

    [UserName] [nvarchar](10) NULL,

    [TransactionKey] [int] NULL,

    [StartDate_Time] [nvarchar](25) NULL,

    [StartDate] [datetime] NULL,

    [StartTime] [time](7) NULL,

    [StartYYYY] [float] NULL,

    [StartMM] [float] NULL,

    [StartDD] [float] NULL,

    [StartHH] [float] NULL,

    [StartNN] [float] NULL,

    [StartSS] [float] NULL,

    [Spare1] [nvarchar](6) NULL,

    [EndDate_Time] [nvarchar](30) NULL,

    [EndDate] [datetime] NULL,

    [EndTime] [time](7) NULL,

    [EndYYYY] [float] NULL,

    [EndMM] [float] NULL,

    [EndDD] [float] NULL,

    [EndHH] [float] NULL,

    [EndNN] [float] NULL,

    [EndSS] [float] NULL,

    [Spare2] [nvarchar](6) NULL,

    [RunTime] [int] NULL,

    [DelayTime] [int] NULL,

    [MachineStopTime] [int] NULL,

    [OperatorStopTime] [int] NULL,

    [MachineFaultTime] [int] NULL,

    [OperatorFaultTime] [int] NULL,

    [OldPiecesFed] [int] NULL,

    [OldPiecesCompleted] [int] NULL,

    [NumMachineStops] [int] NULL,

    [NumOperatorStops] [int] NULL,

    [NumDelays] [int] NULL,

    [Feeder_00_Count] [int] NULL,

    [Feeder_01_Count] [int] NULL,

    [Feeder_02_Count] [int] NULL,

    [Feeder_03_Count] [int] NULL,

    [Feeder_04_Count] [int] NULL,

    [Feeder_05_Count] [int] NULL,

    [Feeder_06_Count] [int] NULL,

    [Feeder_07_Count] [int] NULL,

    [Feeder_08_Count] [int] NULL,

    [Feeder_09_Count] [int] NULL,

    [Feeder_10_Count] [int] NULL,

    [Feeder_11_Count] [int] NULL,

    [Feeder_12_Count] [int] NULL,

    [Feeder_13_Count] [int] NULL,

    [Feeder_14_Count] [int] NULL,

    [Feeder_15_Count] [int] NULL,

    [Feeder_16_Count] [int] NULL,

    [Feeder_17_Count] [int] NULL,

    [Input_Feeder_Count] [int] NULL,

    [Input_SubFdr_1_Count] [int] NULL,

    [Input_SubFdr_2_Count] [int] NULL,

    [Input_SubFdr_3_Count] [int] NULL,

    [Input_SubFdr_4_Count] [int] NULL,

    [Input_SubFdr_5_Count] [int] NULL,

    [Avg_Chassis_Speed] [float] NULL,

    [Shift] [int] NULL,

    [Total_Pcs_Outsorted] [int] NULL,

    [Total_Pcs_Outsorted_Good] [int] NULL,

    [Total_Pcs_Outsorted_Maybe] [int] NULL,

    [Total_Pcs_Outsorted_Bad] [int] NULL,

    [Total_Pcs_Outsorted_Unk] [int] NULL,

    [Bin_01] [int] NULL,

    [Bin_02] [int] NULL,

    [Bin_03] [int] NULL,

    [Bin_04] [int] NULL,

    [Bin_05] [int] NULL,

    [Bin_06] [int] NULL,

    [Bin_07] [int] NULL,

    [Bin_08] [int] NULL,

    [IST_Bin1] [int] NULL,

    [IST_Bin2] [int] NULL,

    [IST_Bin3] [int] NULL,

    [IST_Bin4] [int] NULL,

    [IST_RunOut] [int] NULL,

    [Mtr1_NoPrint] [int] NULL,

    [Mtr2_NoPrint] [int] NULL,

    [Mtr3_NoPrint] [int] NULL,

    [Mtr4_NoPrint] [int] NULL,

    [Edge_Mark1] [int] NULL,

    [Edge_Mark2] [int] NULL,

    [Edge_Mark3] [int] NULL,

    [No_Seal] [int] NULL,

    [Empty_Cycles] [int] NULL,

    [Filled_Cycles] [int] NULL,

    [MidRunTime] [nvarchar](8) NULL,

    [SubShiftExt] [nvarchar](2) NULL,

    [ShiftDateAdjust] [nvarchar](10) NULL,

    [PiecesFed] [int] NULL,

    [PiecesCompleted] [int] NULL,

    [Spare3] [nvarchar](68) NULL,

    [ID] [int] IDENTITY(1,1) NOT NULL,

    CONSTRAINT [PK_ProdDataTB] PRIMARY KEY CLUSTERED

    (

    [ID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    Notice the StartYYYY, StartMM, StartDD, StartHH, StartNN, StartSS. these are what is used I assume to make up the strange string StaerDate_Time.

    Can I take these values join them together and insert into a datetime column ?

    I can then drop these columns from my c# app and have a slick table

    Thanks

    Jay

    As I pointed out on SQL Team - you already have a StartDate and a StartTime column. Why go through all this hassle to tie together the separate date parts when you already have what you need?

    To create a datetime column from the StartDate and StartTime columns it is simply: convert(datetime, StartDate) + convert(datetime, StartTime)

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • September 2, 2012 at 9:35 am

    #1532737

    Hi jeff I made them date time, columns trying to sort this mess out

    Jay

  • September 2, 2012 at 9:38 am

    #1532738

    jerome.morris (9/2/2012)

    Hi jeff I made them date time, columns trying to sort this mess out

    Jay

    Not sure I understand - it probably would help if you showed us the data you are getting and what you are using to put that into SQL.

    It sounds like you are getting data in a text file and putting it into SQL Server. If so, you want to identify the format of the data coming in and either parse it directly into a datetime column or parse it into a date and a time column.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • September 2, 2012 at 10:07 am

    #1532741

    The data I receive is flat fixed width text file, the fastest way I could get this into my DB was to convert to CSV in my c# app and bulk copy to the table. Originally all the columns data types are stringsin ProdData, there is lots of columns and data I dont use but the StartDate_Time format I can work with in my app.

    So I want to create a another table and string together the following

    StartDD StartMM StartYYYY StartHH StartMM StartSS the copy them to a new table into a datetime column.

    Thanks for your support

  • September 2, 2012 at 10:55 am

    #1532750

    CELKO (9/2/2012)

    It gets better look at the create for the table

    I tried to normalize this crap and choked. Most of the columns can be dropped, but I have no idea how to get rid of the repeated groups. Total nightmare. You need a drink and to update your resuem.

    If all you're going to do is be negative and make ad hominem attacks, then just go away. Evenyone including the OP knows there are several problems including normalization and you're comments aren't helping.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 2, 2012 at 10:58 am

    #1532751

    jerome.morris (9/2/2012)

    The data I receive is flat fixed width text file, the fastest way I could get this into my DB was to convert to CSV in my c# app and bulk copy to the table. Originally all the columns data types are stringsin ProdData, there is lots of columns and data I dont use but the StartDate_Time format I can work with in my app.

    So I want to create a another table and string together the following

    StartDD StartMM StartYYYY StartHH StartMM StartSS the copy them to a new table into a datetime column.

    Thanks for your support

    We're getting closer. What we need to see is the record layout for the file, the first 10 lines of the file (including the header if there is one), and what you want for a final table.

    Of course, if the file contains any private data, please obfuscate it before attaching the 10 lines of file data.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 2, 2012 at 11:09 am

    #1532754

    APS08,C5_LAKE_WE,08075_002_,0807500236,ADAMSAG,26013,Sun Jan 01 07:21:59 2012,01/01/2012,07:21:59,2012,01,01,07,21,59,,Sun Jan 01 07:30:42 2012,01/01/2012,07:30:42,2012,01,01,07,30,42,,196,27,88,212,88,212,498,483,3,0,8,00000487,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00001147,00001147,00000000,00000000,00000000,00000000,13463,,2,0,0,2,0,0,2,0,0,0,,,,,,,,,,,,,,,,,488,245,,,,498,483,,

    APS05,C5_LAKE_WE,08066_005_,0806600536,HUGHEPE,28708,Sun Jan 01 07:08:38 2012,01/01/2012,07:08:38,2012,01,01,07,08,38,,Sun Jan 01 07:47:25 2012,01/01/2012,07:47:25,2012,01,01,07,47,25,,1467,45,295,520,295,520,5476,5457,12,2,15,00005467,00000000,00000000,00000000,00000000,00000000,00000109,00004248,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00000000,00009894,00000000,00009894,00000000,00000000,00000000,14090,,18,0,0,14,4,11,2,0,0,5,,,,,,,,,,,,,,,,,5467,275,,,,5476,5457,,

    This is 2 lines from the csv

    the order is the same as the create i posted.

  • September 2, 2012 at 11:22 am

    #1532755

    So, you are getting the data as a distinct date and time. Putting that data into a date column and a time column would be appropriate - and you can then compute the datetime from those two columns as I showed you previously.

    I may have responded with that information on SQL Team instead of here - so I will repeat it here:

    Declare @StartDate date = getdate()

    , @StartTime time = getdate();

    Select @StartDate, @StartTime, convert(datetime, @StartDate) + convert(datetime, @StartTime);

    If your columns are defined as 'StartDate date' and 'StartDate time' - computing the StartDate_Time column is this: convert(datetime, StartDate) + convert(datetime, StartTime).

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • September 2, 2012 at 12:29 pm

    #1532766

    Hi Jeff, sorry I am confused a bit, I get a flat text file, then convert to csv and bulk insert all the data into ProdData table which is all NvarChar.

    What I want to do is use this as a holding table and move data from it to another table that has the correct datatype.

    This is the original create

    USE [SLADB]

    GO

    /****** Object: Table [dbo].[ProdDataTB] Script Date: 09/02/2012 19:28:12 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE TABLE [dbo].[ProdDataTB](

    [MachineName] [nvarchar](10) NULL,

    [ModeName] [nvarchar](10) NULL,

    [FileName] [nvarchar](10) NULL,

    [JobName] [nvarchar](10) NULL,

    [UserName] [nvarchar](10) NULL,

    [TransactionKey] [nvarchar](10) NULL,

    [StartDate_Time] [nvarchar](25) NULL,

    [StartDate] [nvarchar](10) NULL,

    [StartTime] [nvarchar](10) NULL,

    [StartYYYY] [nvarchar](4) NULL,

    [StartMM] [nvarchar](2) NULL,

    [StartDD] [nvarchar](2) NULL,

    [StartHH] [nvarchar](2) NULL,

    [StartNN] [nvarchar](2) NULL,

    [StartSS] [nvarchar](2) NULL,

    [Spare1] [nvarchar](6) NULL,

    [EndDate_Time] [nvarchar](30) NULL,

    [EndDate] [nvarchar](10) NULL,

    [EndTime] [nvarchar](10) NULL,

    [EndYYYY] [nvarchar](4) NULL,

    [EndMM] [nvarchar](2) NULL,

    [EndDD] [nvarchar](2) NULL,

    [EndHH] [nvarchar](2) NULL,

    [EndNN] [nvarchar](2) NULL,

    [EndSS] [nvarchar](2) NULL,

    [Spare2] [nvarchar](6) NULL,

    [RunTime] [nvarchar](5) NULL,

    [DelayTime] [nvarchar](5) NULL,

    [MachineStopTime] [nvarchar](5) NULL,

    [OperatorStopTime] [nvarchar](5) NULL,

    [MachineFaultTime] [nvarchar](5) NULL,

    [OperatorFaultTime] [nvarchar](5) NULL,

    [OldPiecesFed] [nvarchar](5) NULL,

    [OldPiecesCompleted] [nvarchar](5) NULL,

    [NumMachineStops] [nvarchar](5) NULL,

    [NumOperatorStops] [nvarchar](5) NULL,

    [NumDelays] [nvarchar](5) NULL,

    [Feeder_00_Count] [nvarchar](8) NULL,

    [Feeder_01_Count] [nvarchar](8) NULL,

    [Feeder_02_Count] [nvarchar](8) NULL,

    [Feeder_03_Count] [nvarchar](8) NULL,

    [Feeder_04_Count] [nvarchar](8) NULL,

    [Feeder_05_Count] [nvarchar](8) NULL,

    [Feeder_06_Count] [nvarchar](8) NULL,

    [Feeder_07_Count] [nvarchar](8) NULL,

    [Feeder_08_Count] [nvarchar](8) NULL,

    [Feeder_09_Count] [nvarchar](8) NULL,

    [Feeder_10_Count] [nvarchar](8) NULL,

    [Feeder_11_Count] [nvarchar](8) NULL,

    [Feeder_12_Count] [nvarchar](8) NULL,

    [Feeder_13_Count] [nvarchar](8) NULL,

    [Feeder_14_Count] [nvarchar](8) NULL,

    [Feeder_15_Count] [nvarchar](8) NULL,

    [Feeder_16_Count] [nvarchar](8) NULL,

    [Feeder_17_Count] [nvarchar](8) NULL,

    [Input_Feeder_Count] [nvarchar](8) NULL,

    [Input_SubFdr_1_Count] [nvarchar](8) NULL,

    [Input_SubFdr_2_Count] [nvarchar](8) NULL,

    [Input_SubFdr_3_Count] [nvarchar](8) NULL,

    [Input_SubFdr_4_Count] [nvarchar](8) NULL,

    [Input_SubFdr_5_Count] [nvarchar](8) NULL,

    [Avg_Chassis_Speed] [nvarchar](5) NULL,

    [Shift] [nvarchar](5) NULL,

    [Total_Pcs_Outsorted] [nvarchar](5) NULL,

    [Total_Pcs_Outsorted_Good] [nvarchar](5) NULL,

    [Total_Pcs_Outsorted_Maybe] [nvarchar](5) NULL,

    [Total_Pcs_Outsorted_Bad] [nvarchar](5) NULL,

    [Total_Pcs_Outsorted_Unk] [nvarchar](5) NULL,

    [Bin_01] [nvarchar](5) NULL,

    [Bin_02] [nvarchar](5) NULL,

    [Bin_03] [nvarchar](5) NULL,

    [Bin_04] [nvarchar](5) NULL,

    [Bin_05] [nvarchar](5) NULL,

    [Bin_06] [nvarchar](5) NULL,

    [Bin_07] [nvarchar](5) NULL,

    [Bin_08] [nvarchar](5) NULL,

    [IST_Bin1] [nvarchar](5) NULL,

    [IST_Bin2] [nvarchar](5) NULL,

    [IST_Bin3] [nvarchar](5) NULL,

    [IST_Bin4] [nvarchar](5) NULL,

    [IST_RunOut] [nvarchar](5) NULL,

    [Mtr1_NoPrint] [nvarchar](5) NULL,

    [Mtr2_NoPrint] [nvarchar](5) NULL,

    [Mtr3_NoPrint] [nvarchar](5) NULL,

    [Mtr4_NoPrint] [nvarchar](5) NULL,

    [Edge_Mark1] [nvarchar](5) NULL,

    [Edge_Mark2] [nvarchar](5) NULL,

    [Edge_Mark3] [nvarchar](5) NULL,

    [No_Seal] [nvarchar](5) NULL,

    [Empty_Cycles] [nvarchar](6) NULL,

    [Filled_Cycles] [nvarchar](6) NULL,

    [MidRunTime] [nvarchar](8) NULL,

    [SubShiftExt] [nvarchar](2) NULL,

    [ShiftDateAdjust] [nvarchar](10) NULL,

    [PiecesFed] [nvarchar](6) NOT NULL,

    [PiecesCompleted] [nvarchar](6) NULL,

    [Spare3] [nvarchar](68) NULL,

    [ID] [int] IDENTITY(1,1) NOT NULL,

    CONSTRAINT [PK_ProdDataTB] PRIMARY KEY CLUSTERED

    (

    [ID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    Could you provide a sample of how to copy startdate_time in correct format and copy to table b column b for example

    thanks again

Viewing 15 posts - 16 through 30 (of 46 total)

You must be logged in to reply to this topic. Login to reply