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

returning all dates that match year and month Expand / Collapse
Author
Message
Posted Thursday, November 15, 2012 8:09 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
SELECT MachineName, ModeName, FileName, UserName, StartDate, StartTime, EndDate, EndTime, RunTime, DelayTime, MachineStopTime, OperatorStopTime,
MachineFaultTime, OperatorFaultTime, NumMachineStops, NumOperatorStops, NumDelays, Feeder00Count, InputFeederCount, AvgChassisSpeed,
TotalPcsOutsorted, TotalPcsOutsortedGood, TotalPcsOutsortedMaybe, TotalPcsOutsortedBad, TotalPcsOutsortedUnk, FilledCycles, EmptyCycles, PiecesFed,
PiecesCompleted, Id
FROM ProdDataTB
WHERE (DATEPART(yyyy, @date1) = DATEPART(yyyy, EndDate)) AND (DATEPART(mm, @date1) = DATEPART(mm, EndDate))

What am I doing wrong I only want to return the year and month that match

10/10/2012 so all end dates in oct for the year 2012

Without converting! the EndDate is datetime column

Thanks

J
Post #1385182
Posted Thursday, November 15, 2012 8:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 12,995, Visits: 12,414
jerome.morris (11/15/2012)
SELECT MachineName, ModeName, FileName, UserName, StartDate, StartTime, EndDate, EndTime, RunTime, DelayTime, MachineStopTime, OperatorStopTime,
MachineFaultTime, OperatorFaultTime, NumMachineStops, NumOperatorStops, NumDelays, Feeder00Count, InputFeederCount, AvgChassisSpeed,
TotalPcsOutsorted, TotalPcsOutsortedGood, TotalPcsOutsortedMaybe, TotalPcsOutsortedBad, TotalPcsOutsortedUnk, FilledCycles, EmptyCycles, PiecesFed,
PiecesCompleted, Id
FROM ProdDataTB
WHERE (DATEPART(yyyy, @date1) = DATEPART(yyyy, EndDate)) AND (DATEPART(mm, @date1) = DATEPART(mm, EndDate))

What am I doing wrong I only want to return the year and month that match

10/10/2012 so all end dates in oct for the year 2012

Without converting! the EndDate is datetime column

Thanks

J


I don't see that you are doing anything wrong at least logically. You have however put together a nonSARGable where clause. How about this instead?

Where EndDate > dateadd(mm, datediff(mm, 0, @date1), 0)
and EndDate < dateadd(mm, datediff(mm, 0, @date1) + 1, 0)



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1385212
Posted Thursday, November 15, 2012 3:00 PM
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 Sean, am I being dumb? I cant see how it matches the year ?

Post #1385374
Posted Thursday, November 15, 2012 3:42 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 11:09 PM
Points: 3,108, Visits: 11,502
jerome.morris (11/15/2012)
Hi Sean, am I being dumb? I cant see how it matches the year ?



Since it only selects data greater than the start of the month and before the start of the next month, it has to be in the same year.

You should try testing with it to see if it works for you.

Post #1385385
Posted Thursday, November 15, 2012 3:51 PM
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 Micheal, I did test it and it worked but wanted to understand how it was working

Thanks

J
Post #1385387
Posted Friday, November 16, 2012 7:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 12,995, Visits: 12,414
jerome.morris (11/15/2012)
Hi Micheal, I did test it and it worked but wanted to understand how it was working

Thanks

J


Jerome,

I borrowed heavily from the article Lynn Pettis wrote on dates. http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/ Read the comments in that article. It will explain the logic. Let me know if you need any further explanation.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1385712
Posted Monday, November 19, 2012 9:04 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, December 19, 2013 12:26 AM
Points: 14, Visits: 52
Hi Jerome!

You don't need to use just to match the year and the month of your parameter. Try this query

SELECT MachineName, ModeName, FileName, UserName, StartDate, StartTime, EndDate, EndTime, RunTime, DelayTime, MachineStopTime, OperatorStopTime,
MachineFaultTime, OperatorFaultTime, NumMachineStops, NumOperatorStops, NumDelays, Feeder00Count, InputFeederCount, AvgChassisSpeed,
TotalPcsOutsorted, TotalPcsOutsortedGood, TotalPcsOutsortedMaybe, TotalPcsOutsortedBad, TotalPcsOutsortedUnk, FilledCycles, EmptyCycles, PiecesFed,
PiecesCompleted, Id
FROM ProdDataTB
WHERE (YEAR(EndDate) = YEAR(@date1)) AND (MONTH(EndDate) = MONTH(@date1))


Hope i can help
Post #1386662
Posted Tuesday, November 20, 2012 7:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:13 PM
Points: 12,995, Visits: 12,414
math martinez (11/19/2012)
Hi Jerome!

You don't need to use just to match the year and the month of your parameter. Try this query

SELECT MachineName, ModeName, FileName, UserName, StartDate, StartTime, EndDate, EndTime, RunTime, DelayTime, MachineStopTime, OperatorStopTime,
MachineFaultTime, OperatorFaultTime, NumMachineStops, NumOperatorStops, NumDelays, Feeder00Count, InputFeederCount, AvgChassisSpeed,
TotalPcsOutsorted, TotalPcsOutsortedGood, TotalPcsOutsortedMaybe, TotalPcsOutsortedBad, TotalPcsOutsortedUnk, FilledCycles, EmptyCycles, PiecesFed,
PiecesCompleted, Id
FROM ProdDataTB
WHERE (YEAR(EndDate) = YEAR(@date1)) AND (MONTH(EndDate) = MONTH(@date1))


Hope i can help


This is a little easier to read than the original post but has the same issues with SARGability. It will cause a full scan of the table because the functions in the where clause are referencing columns in the table. That means the optimizer has to evaluate every single row to see if it is a match.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1386903
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse