Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Date Query Format Problem Expand / Collapse
Author
Message
Posted Friday, August 31, 2012 6:52 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 3:01 AM
Points: 147, Visits: 161
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')
Post #1352771
Posted Friday, August 31, 2012 6:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:06 AM
Points: 7,447, Visits: 15,699
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.

“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
Exploring Recursive CTEs by Example Dwain Camps
Post #1352781
Posted Friday, August 31, 2012 7:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:06 AM
Points: 7,447, Visits: 15,699
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




“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
Exploring Recursive CTEs by Example Dwain Camps
Post #1352796
Posted Friday, August 31, 2012 7:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 3:01 AM
Points: 147, Visits: 161
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?
Post #1352824
Posted Friday, August 31, 2012 8:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:06 AM
Points: 7,447, Visits: 15,699
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



“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
Exploring Recursive CTEs by Example Dwain Camps
Post #1352841
Posted Saturday, September 1, 2012 5:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 3:01 AM
Points: 147, Visits: 161
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
Post #1353140
Posted Saturday, September 1, 2012 10:53 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:17 PM
Points: 38,307, Visits: 35,219
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
"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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1353149
Posted Saturday, September 1, 2012 11:02 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:17 PM
Points: 38,307, Visits: 35,219
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
"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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1353151
Posted Saturday, September 1, 2012 11:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 19, 2014 3:01 AM
Points: 147, Visits: 161
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 ?


Post #1353152
Posted Saturday, September 1, 2012 12:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:17 PM
Points: 38,307, Visits: 35,219
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 sense

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1353157
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse