SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Date Query Format Problem


Date Query Format Problem

Author
Message
jerome.morris
jerome.morris
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 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')
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16154 Visits: 19542
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
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16154 Visits: 19542
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
jerome.morris
jerome.morris
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 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?
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16154 Visits: 19542
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
jerome.morris
jerome.morris
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85485 Visits: 41081
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85485 Visits: 41081
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. :-D

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
jerome.morris
jerome.morris
SSC Veteran
SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)SSC Veteran (211 reputation)

Group: General Forum Members
Points: 211 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 ?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search