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: 2 days ago @ 1:43 AM
Points: 6,890, Visits: 14,254
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: 2 days ago @ 1:43 AM
Points: 6,890, Visits: 14,254
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: 2 days ago @ 1:43 AM
Points: 6,890, Visits: 14,254
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 Friday, August 31, 2012 9:59 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:12 PM
Points: 1,945, Visits: 3,180
format in this column is like so: 'Wed Sep 01 03:00:00 2010' something_start_timestamp is a string also I have no control over this <<

I changed the data name to ISO-11179 rules. We have a DATETIME2(n) data type. I also get a laugh out of “Prod_Data_Tb”; SQL only has tables and table can only hold data, so the postfixes are an insane redundancy. Why are you using a totally bizarre string? There has to be a good story. The happy ending will be when you kill the moron that did this to you.

The best way I have found for this kind of crap is to build a look-up table with LIKE patterns:

CREATE TABLE Screwed_Timestamps
(cal_date DATE NOT NULL PRIMARY KEY,
screwedup_date_pattern CHAR(nn) NOT NULL);

INSERT INTO Screwed_Timestamps
VALUES ('2010-09-01', '__ SEP 01 __:__:__ 2010'),
(..), etc;

SELECT ..
FROM Products AS P, Screwed_Timestamps AS T
WHERE P.screwed_something_date
LIKE T.screwedup_date_pattern;


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1352945
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: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
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: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
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
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse