Date Query Format Problem

Question

Date Query Format Problem

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

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

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

jerome.morris SSCrazy Points: 2829 More actions · Answer 2

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

Jay

jerome.morris SSCrazy Points: 2829 More actions · Answer 3

Its not dropped 🙁

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

from ProdDataTB

Thanks

jerome.morris SSCrazy Points: 2829 More actions · Answer 4

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

from ProdDataTB

jerome.morris SSCrazy Points: 2829 More actions · Answer 5

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 6

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

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)

jerome.morris SSCrazy Points: 2829 More actions · Answer 8

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

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

jerome.morris SSCrazy Points: 2829 More actions · Answer 10

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

jerome.morris SSCrazy Points: 2829 More actions · Answer 11

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

jerome.morris SSCrazy Points: 2829 More actions · Answer 12

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 14

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 15

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