Post reply

Date Query Format Problem

  • September 2, 2012 at 12:48 pm

    #1532768

    jerome.morris (9/2/2012)

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

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

    This is 2 lines from the csv

    the order is the same as the create i posted.

    Okay - I have stripped out all of the additional information from the file so we can just look at first set of dates (Start Dates).

    In the above string of data - you have the following: ,Sun Jan 01 07:21:59 2012,01/01/2012,07:21:59,

    The first field is a string representation of a datetime, the second field is the date only and the third field is the time only. Now why do you even need to worry about the string date time when you already have the date as a distinct value and the time as a distinct value?

    Stop worrying about how to manipulate the datepart values to get a datetime and just use the distinct date and time values.

    In your table create statement, you are creating a StartDate column as date and a StartTime column as time. This is correct and all you really need to worry about. Once you have those values in the table - you can always combine them using the code I provided to get the full datetime.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • September 2, 2012 at 1:13 pm

    #1532771

    Ah magic I think the pennie has dropped. I will post what I have so you can advise if that's ok.

    Jay

  • September 2, 2012 at 2:14 pm

    #1532775

    Its not dropped 🙁

    you declaring at the start but how how do I tell it to use StartDate ?

    from ProdDataTB

    Thanks

  • September 2, 2012 at 2:19 pm

    #1532776

    Select StartDate, StartTime, convert(datetime, StartDate) + convert(datetime, StartTime) AS t

    from ProdDataTB

  • September 2, 2012 at 2:56 pm

    #1532778

    To Make life easier is it possible to replace the date in StartDate_Time with the results returned from

    Select StartDate, StartTime, convert(datetime, StartDate) + convert(datetime, StartTime) AS StartDate_Time

    from ProdDataTB

    Thanks

  • September 2, 2012 at 7:36 pm

    #1532792

    jerome.morris (9/2/2012)

    To Make life easier is it possible to replace the date in StartDate_Time with the results returned from

    Select StartDate, StartTime, convert(datetime, StartDate) + convert(datetime, StartTime) AS StartDate_Time

    from ProdDataTB

    Thanks

    Not sure why you would need to - since this is just a staging table. I am not sure why you would need or want a datetime column in your final table, but you could easily create a computed column - and even make it persisted. That would allow the column to be searchable on an index.

    I wouldn't bother with that - unless you have a lot of queries that would be querying on both date and time. Even then, I am not sure it would be worth it when you can query both columns easier than the combined datetime column.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

  • September 2, 2012 at 11:50 pm

    #1532819

    jerome.morris (9/2/2012)

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

    We getting closer still. Now we just need to see is the Creaate Table statement for the "another table that has the correct datatype".

    --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)
    Intro to Tally Tables and Functions

  • September 3, 2012 at 4:26 am

    #1532893

    Hi Jefff, where am I going wrong with this query please

    SELECT MachineName, ModeName, FileName, JobName, UserName,

    StartDate, StartTime, CONVERT(datetime, StartDate) + CONVERT(datetime, StartTime) AS StartDate_Time,

    EndDate, EndTime, CONVERT(datetime, EndDate) + CONVERT(datetime, EndTime) AS EndDate_Time,

    RunTime, DelayTime, MachineStopTime, OperatorStopTime, MachineFaultTime, OperatorFaultTime, OldPiecesFed, OldPiecesCompleted,

    NumMachineStops, NumOperatorStops, NumDelays, Feeder_00_Count, Feeder_01_Count, Feeder_02_Count, Feeder_03_Count, Feeder_04_Count,

    Feeder_05_Count, Feeder_06_Count, Feeder_07_Count, Feeder_08_Count, Feeder_09_Count, Feeder_10_Count, Feeder_11_Count, Feeder_12_Count,

    Feeder_13_Count, Feeder_14_Count, Feeder_15_Count, Feeder_16_Count, Feeder_17_Count, Input_Feeder_Count, Input_SubFdr_1_Count, Input_SubFdr_2_Count,

    Input_SubFdr_3_Count, Input_SubFdr_4_Count, Input_SubFdr_5_Count, Avg_Chassis_Speed, Shift, Total_Pcs_Outsorted, Total_Pcs_Outsorted_Good,

    Total_Pcs_Outsorted_Maybe, Total_Pcs_Outsorted_Bad, Total_Pcs_Outsorted_Unk, Bin_01, Bin_02, Bin_03, Bin_04, Bin_05, Bin_06, Bin_07, Bin_08, IST_Bin1,

    IST_Bin2, IST_Bin3, IST_Bin4, IST_RunOut, Mtr1_NoPrint, Mtr2_NoPrint, Mtr3_NoPrint, Mtr4_NoPrint, Edge_Mark1, Edge_Mark2, Edge_Mark3, No_Seal,

    Empty_Cycles, Filled_Cycles, MidRunTime, PiecesFed, PiecesCompleted, ID

    FROM ProdDataTB

    WHERE StartDate_Time >= 06/01/2012 And EndDate_Time <= 06/01/2012 ;

    The Where Clause

  • September 3, 2012 at 5:04 am

    #1532906

    jerome.morris (9/3/2012)

    Hi Jefff, where am I going wrong with this query please ...

    Jay, you are likely to progress far more quickly with this problem - the date format problem - by answering Jeff's (and others') questions than by posting random queries with syntax errors.

    You are importing a file; the end result is a table which is used for reporting.

    Import the file into a staging table. Do some processing on the staging table if you need to. Import from the staging table into the reporting table, doing more processing if you need to. The two most important bits are straightforward:

    1. The staging table should be structured to discourage load failures (from the source file) – it’s usually easiest to map column to column and row to row between source and target, and often easiest to import into VARCHAR or NVARCHAR columns because they will accept rubbish whereas DATETIME will not. Get the data in, don’t worry about data type yet.

    2. The processing stage(s) should discourage load failures (load to reporting table) by replacing rubbish with NULL.

    What we need to see is the DDL for the staging table and the reporting table. This will enable folks to help you determine where to implement the processing code but more importantly what code to use.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • September 3, 2012 at 5:11 am

    #1532910

    If I Convert and use the AS can I run a query against it ?

    SELECT MachineName, ModeName, FileName, JobName, UserName,

    CONVERT(datetime, StartDate) AS StartJob, StartTime,

    CONVERT(datetime, EndDate) AS EndJob, EndTime,

    RunTime, DelayTime, MachineStopTime, OperatorStopTime, MachineFaultTime, OperatorFaultTime, OldPiecesFed, OldPiecesCompleted,

    NumMachineStops, NumOperatorStops, NumDelays, Feeder_00_Count, Feeder_01_Count, Feeder_02_Count, Feeder_03_Count, Feeder_04_Count,

    Feeder_05_Count, Feeder_06_Count, Feeder_07_Count, Feeder_08_Count, Feeder_09_Count, Feeder_10_Count, Feeder_11_Count, Feeder_12_Count,

    Feeder_13_Count, Feeder_14_Count, Feeder_15_Count, Feeder_16_Count, Feeder_17_Count, Input_Feeder_Count, Input_SubFdr_1_Count, Input_SubFdr_2_Count,

    Input_SubFdr_3_Count, Input_SubFdr_4_Count, Input_SubFdr_5_Count, Avg_Chassis_Speed, Shift, Total_Pcs_Outsorted, Total_Pcs_Outsorted_Good,

    Total_Pcs_Outsorted_Maybe, Total_Pcs_Outsorted_Bad, Total_Pcs_Outsorted_Unk, Bin_01, Bin_02, Bin_03, Bin_04, Bin_05, Bin_06, Bin_07, Bin_08, IST_Bin1,

    IST_Bin2, IST_Bin3, IST_Bin4, IST_RunOut, Mtr1_NoPrint, Mtr2_NoPrint, Mtr3_NoPrint, Mtr4_NoPrint, Edge_Mark1, Edge_Mark2, Edge_Mark3, No_Seal,

    Empty_Cycles, Filled_Cycles, MidRunTime, SubShiftExt, ShiftDateAdjust, PiecesFed, PiecesCompleted, Spare3, ID

    FROM ProdDataTB

    WHERE (StartJob >= 06 / 01 / 2012) AND (EndJob <= 06 / 01 / 2012)

    the above tells me the column dosent exist ?

    Thanks

  • September 3, 2012 at 5:15 am

    #1532912

    Thanks Chris, sorry to post and post but I feel like I am close. I dont have a holing table anymore and the only problem I have is running queries against my table as mentioned just before this.

    A pain for you experts but if I jump straight to a solution I wont understand anything. I want to understand why I cant run a query against a Column I have declared using AS, or is this not how it works ?

    Thanks guys

    Jay

  • September 3, 2012 at 5:22 am

    #1532916

    Right I think I was doing the convert in the wrong place, this returns results I want to see.

    SELECT MachineName, ModeName, FileName, JobName, UserName, StartDate, StartTime,EndDate,

    EndTime, RunTime, DelayTime, MachineStopTime, OperatorStopTime, MachineFaultTime, OperatorFaultTime, OldPiecesFed, OldPiecesCompleted,

    NumMachineStops, NumOperatorStops, NumDelays, Feeder_00_Count, Feeder_01_Count, Feeder_02_Count, Feeder_03_Count, Feeder_04_Count,

    Feeder_05_Count, Feeder_06_Count, Feeder_07_Count, Feeder_08_Count, Feeder_09_Count, Feeder_10_Count, Feeder_11_Count, Feeder_12_Count,

    Feeder_13_Count, Feeder_14_Count, Feeder_15_Count, Feeder_16_Count, Feeder_17_Count, Input_Feeder_Count, Input_SubFdr_1_Count, Input_SubFdr_2_Count,

    Input_SubFdr_3_Count, Input_SubFdr_4_Count, Input_SubFdr_5_Count, Avg_Chassis_Speed, Shift, Total_Pcs_Outsorted, Total_Pcs_Outsorted_Good,

    Total_Pcs_Outsorted_Maybe, Total_Pcs_Outsorted_Bad, Total_Pcs_Outsorted_Unk, Bin_01, Bin_02, Bin_03, Bin_04, Bin_05, Bin_06, Bin_07, Bin_08, IST_Bin1,

    IST_Bin2, IST_Bin3, IST_Bin4, IST_RunOut, Mtr1_NoPrint, Mtr2_NoPrint, Mtr3_NoPrint, Mtr4_NoPrint, Edge_Mark1, Edge_Mark2, Edge_Mark3, No_Seal,

    Empty_Cycles, Filled_Cycles, MidRunTime, SubShiftExt, ShiftDateAdjust, PiecesFed, PiecesCompleted, Spare3, ID

    FROM ProdDataTB

    WHERE (CONVERT(datetime, StartDate) >= '06/06/2012') AND (CONVERT(datetime,EndDate) <= '06/06/2012')

  • September 3, 2012 at 5:22 am

    #1532917

    jerome.morris (9/3/2012)

    Thanks Chris, sorry to post and post but I feel like I am close. I dont have a holing table anymore and the only problem I have is running queries against my table as mentioned just before this.

    A pain for you experts but if I jump straight to a solution I wont understand anything. I want to understand why I cant run a query against a Column I have declared using AS, or is this not how it works ?

    Thanks guys

    Jay

    Do you mean; why you can't use, in the WHERE clause, a column alias created in the output list (the SELECT)?

    It's because the WHERE clause is interpreted before the column alias is assigned. There are numerous ways around this - it's not a limitation.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • September 3, 2012 at 5:32 am

    #1532922

    jerome.morris (9/3/2012)

    Right I think I was doing the convert in the wrong place, this returns results I want to see.

    SELECT MachineName, ModeName, FileName, JobName, UserName, StartDate, StartTime,EndDate,

    EndTime, RunTime, DelayTime, MachineStopTime, OperatorStopTime, MachineFaultTime, OperatorFaultTime, OldPiecesFed, OldPiecesCompleted,

    NumMachineStops, NumOperatorStops, NumDelays, Feeder_00_Count, Feeder_01_Count, Feeder_02_Count, Feeder_03_Count, Feeder_04_Count,

    Feeder_05_Count, Feeder_06_Count, Feeder_07_Count, Feeder_08_Count, Feeder_09_Count, Feeder_10_Count, Feeder_11_Count, Feeder_12_Count,

    Feeder_13_Count, Feeder_14_Count, Feeder_15_Count, Feeder_16_Count, Feeder_17_Count, Input_Feeder_Count, Input_SubFdr_1_Count, Input_SubFdr_2_Count,

    Input_SubFdr_3_Count, Input_SubFdr_4_Count, Input_SubFdr_5_Count, Avg_Chassis_Speed, Shift, Total_Pcs_Outsorted, Total_Pcs_Outsorted_Good,

    Total_Pcs_Outsorted_Maybe, Total_Pcs_Outsorted_Bad, Total_Pcs_Outsorted_Unk, Bin_01, Bin_02, Bin_03, Bin_04, Bin_05, Bin_06, Bin_07, Bin_08, IST_Bin1,

    IST_Bin2, IST_Bin3, IST_Bin4, IST_RunOut, Mtr1_NoPrint, Mtr2_NoPrint, Mtr3_NoPrint, Mtr4_NoPrint, Edge_Mark1, Edge_Mark2, Edge_Mark3, No_Seal,

    Empty_Cycles, Filled_Cycles, MidRunTime, SubShiftExt, ShiftDateAdjust, PiecesFed, PiecesCompleted, Spare3, ID

    FROM ProdDataTB

    WHERE (CONVERT(datetime, StartDate) >= '06/06/2012') AND (CONVERT(datetime,EndDate) <= '06/06/2012')

    It will work, but the performance will suck - and indexing startdate and enddate won't help.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • September 3, 2012 at 8:42 am

    #1532997

    Jay, you still are stuck with the idea that you need a datetime column for your query. Why? Is there a reason you believe you have to query on that?

    In your query you are only querying on the date portion - so, use the date column.

    WHERE StartDate >= '06/06/2012

    AND StartDate <= '06/06/2012'

    Or - since it is a date column without a time component you can do this:

    WHERE StartDate BETWEEN '06/06/2012' AND '06/06/2012'

    Note: you would be much better off using a non-ambiguous date format. That would be either: YYYY-MM-DD or YYYYMMDD - and YYYYMMDD is probably safer depending on the version of SQL Server you are running against.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

Viewing 15 posts - 31 through 45 (of 46 total)

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