August 31, 2012 at 6:52 am
Hi all, my database has a column called StartDate_Time and the format in this column is like so
Wed Sep 01 03:00:00 2010
I am trying to query all result for a single day from 00:00 to 23:59 on a given date.
This is what I have tried but returns no result, any ideas.
StartDate_Time is a string also i have no control over this
SELECT * from SLADB.dbo.ProdDataTB
where MachineName =('APS08')
AND StartDate_Time >= ('06/01/2012 00:00') AND EndDate_Time <('06/01/2012 23:59')
August 31, 2012 at 6:59 am
Jerome, can you post the CREATE TABLE script for SLADB.dbo.ProdDataTB? Remove the other columns if you wish. To get the script, right-click on the table in object explorer in SSMS.
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
August 31, 2012 at 7:23 am
SELECT
d.StartDate_Time,
date_string = p.[Year]+'-'+p.MonthNum+'-'+p.[Day]+' '+p.[Time],
date_datetime = CONVERT(DATETIME,p.[Year]+'-'+p.MonthNum+'-'+p.[Day]+' '+p.[Time],120)
FROM (SELECT StartDate_Time = 'Wed Sep 01 03:00:00 2010') d
CROSS APPLY (
SELECT
[Year] = RIGHT(d.StartDate_Time,4),
x.MonthNum,
[Day] = SUBSTRING(d.StartDate_Time,9,2),
[Time] = SUBSTRING(d.StartDate_Time,12,8)
FROM (VALUES ('01','Jan'),('02','Feb'),('03','Mar'),('04','Apr'),('05','May'),('06','Jun'),
('07','Jul'),('08','Aug'),('09','Sep'),('10','Oct'),('11','Nov'),('12','Dec')) x (MonthNum, MonthName)
WHERE MonthName = SUBSTRING(d.StartDate_Time,5,3)
) p
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
August 31, 2012 at 7:56 am
Hi Chris, well that works for bring that date I added my part to the top
SELECT * from SLADB.dbo.ProdDataTB
where MachineName = ('APS01')
Select
d.StartDate_Time,
date_string = p.[Year]+'-'+p.MonthNum+'-'+p.[Day]+' '+p.[Time],
date_datetime = CONVERT(DATETIME,p.[Year]+'-'+p.MonthNum+'-'+p.[Day]+' '+p.[Time],120)
FROM (SELECT StartDate_Time = 'Wed Sep 01 03:00:00 2010') d
CROSS APPLY (
SELECT
[Year] = RIGHT(d.StartDate_Time,4),
x.MonthNum,
[Day] = SUBSTRING(d.StartDate_Time,9,2),
[Time] = SUBSTRING(d.StartDate_Time,12,8)
FROM (VALUES ('01','Jan'),('02','Feb'),('03','Mar'),('04','Apr'),('05','May'),('06','Jun'),
('07','Jul'),('08','Aug'),('09','Sep'),('10','Oct'),('11','Nov'),('12','Dec')) x (MonthNum, MonthName)
WHERE MonthName = SUBSTRING(d.StartDate_Time,5,3)
) p
How do I get everything for that day given the fact that a job could start at 23:00 and finish at 01:00 ht next day which the above query would include.
So the start should be from for example
StartDate_Time 06/06/2012 00:00
EndDate_Time 06/06/2212 23:59
everything between these these dates inc hours ?
Does this make sense?
August 31, 2012 at 8:19 am
DECLARE @StartDate_Time DATETIME, @EndDate_Time DATETIME
SELECT @StartDate_Time = CONVERT(DATETIME,'06/06/2012',103), @EndDate_Time = CONVERT(DATETIME,'07/06/2012',103)
SELECT @StartDate_Time, @EndDate_Time
SELECT
x.ProperStartDateTime,
d.*
FROM SLADB.dbo.ProdDataTB d
CROSS APPLY (
SELECT
[Year] = RIGHT(d.StartDate_Time,4),
x.MonthNum,
[Day] = SUBSTRING(d.StartDate_Time,9,2),
[Time] = SUBSTRING(d.StartDate_Time,12,8)
FROM (VALUES ('01','Jan'),('02','Feb'),('03','Mar'),('04','Apr'),('05','May'),('06','Jun'),
('07','Jul'),('08','Aug'),('09','Sep'),('10','Oct'),('11','Nov'),('12','Dec')) x (MonthNum, MonthName)
WHERE MonthName = SUBSTRING(d.StartDate_Time,5,3)
) p
CROSS APPLY (SELECT ProperStartDateTime = CONVERT(DATETIME,p.[Year]+'-'+p.MonthNum+'-'+p.[Day]+' '+p.[Time],120)) x
WHERE d.MachineName = 'APS01'
AND x.ProperStartDateTime >= @StartDate_Time AND x.ProperStartDateTime < @EndDate_Time
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 1, 2012 at 5:34 am
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
September 1, 2012 at 10:53 am
jerome.morris (9/1/2012)
Can I take these values join them together and insert into a datetime column ?
Yes but you said the following...
StartDate_Time is a string also i have no control over this
Unless someone wrote some really awful code on the front end where INSERTs don't have a column list as a part of the INSERT, you should be able to add a PERSISTED computed column to the table where the date/time is cast as a DATETIME datatype. That would be almost as good as removing all the unnecessary columns, which you apparently aren't allowed to do.
If adding such a computed column to the table would break the front-end code for the reason previously stated, then you could create a view to accomplish the same thing. You could "materialize" the view with the understanding that will nearly double the storage requirement but would be very good for performance if it were indexed correctly.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 1, 2012 at 11:02 am
CELKO (8/31/2012)
The happy ending will be when you kill the moron that did this to you.
BWAAA-HAAA!!!! Normally, I take exception to you saying such things but you happily didn't blame the OP for this mess this time. With that thought in mind, I thoroughly agree... whoever designed this table is a permanent resident of the shallow and very muddy end of the gene pool and should be kept from perpetuating the species. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
September 1, 2012 at 11:05 am
I was think thinking of removing all the data in the StartDate_Time column and adding the StartYYYY,StartMM,StartDD, StartHH, StartMM and populating StartDate_Time. Does this make sense
like coalesce prehaps if that possible ?
September 1, 2012 at 12:17 pm
jerome.morris (9/1/2012)
I was think thinking of removing all the data in the StartDate_Time column and adding the StartYYYY,StartMM,StartDD, StartHH, StartMM and populating StartDate_Time. Does this make senselike coalesce prehaps if that possible ?
I don't believe COALESCE is going to help anything here because that's just a check for nulls. Removing the data from the columns won't buy you anything either because they're FLOAT datatypes which allocates disk space whether they contain data or not.
I thought you said that things like having a string date was beyond your control. That would seem to also indicate that if you just go in and delete a bunch of data that you're going to break something.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 1, 2012 at 12:29 pm
Sorry I mean at the no control over the the way I receive the data. I take the file convert to csv then bulk insert. lots of the columns I will not need after.
I can change all the columns to Nvarchar if need be. They are like they are because I was playing around, what would you recommend me to do. I need real date time columns from the DB for reportviewer. Can I join the columns then convert them to DateTime and insert into the StartDate_Time Column ?
Thanks for all your support
J
September 1, 2012 at 1:01 pm
So the ProdDataTB table is just a staging table that you're bulk inserting into? What do you want the final table that you're going to use against the front end to look like?
--Jeff Moden
Change is inevitable... Change for the better is not.
September 1, 2012 at 1:25 pm
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
September 1, 2012 at 3:02 pm
I can use this
select StartDD+'/'+StartMM+'/'+StartYYYY from ProdDataTB AS T;
how then do I overwrite the StartDate_Time column with the returned T and convert to datetime ?
September 1, 2012 at 3:08 pm
insert into dbo.ProdDataTB(StartDate_Time)
select StartDD+'-'+StartMM+'-'+StartYYYY+' '+StartHH+':'+StartMM+':'+StartSS from ProdDataTB AS T;
this tells me x amount of rows effected but doesnt really change the StartDate_Time column
Viewing 15 posts - 1 through 15 (of 46 total)
You must be logged in to reply to this topic. Login to reply