Author |
Topic |
Picassochick
Starting Member
2 Posts |
Posted - 2012-10-25 : 11:54:25
|
Hi,Complete novice to this so please bear with me...have a view setup in SQL 2005 (see SQL below). How or can I add an extra field that will convert tShift from DateTime to Date only i.e. 25-10-2012 12:36 to 25-10-2012 only ?Thanks,SELECT lOEEWorkCellId, sDescription, tShift, sPartId, dRunSec, dPartCount, dDownTimeSec, dIdealCycleTime, dTotalParts, tStart, sShift, dAvailSecFROM dbo.OEEQWorkCellWHERE tStart >= DATEADD(d, - 14, { fn NOW() })) AND (sShift = 'Shift 1' OR sShift = 'Shift 2' OR sShift = 'Shift 3') AND (dAvailSec <> 0) |
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-10-25 : 12:14:50
|
See changes in red belowSELECT lOEEWorkCellId, sDescription, tShift, sPartId, dRunSec, dPartCount, dDownTimeSec, dIdealCycleTime, dTotalParts, tStart, sShift, dAvailSec, DATEADD(dd,DATEDIFF(dd,0,tShift),0) AS tShiftDateFROM dbo.OEEQWorkCellWHERE tStart >= DATEADD(d, - 14, GETDATE()) AND ( sShift = 'Shift 1' OR sShift = 'Shift 2' OR sShift = 'Shift 3' ) AND (dAvailSec <> 0) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-25 : 12:15:20
|
[code]SELECT lOEEWorkCellId, sDescription, tShift,DATEADD(dd,DATEDIFF(dd,0,tShift),0) AS tShiftDateOnly, sPartId, dRunSec, dPartCount, dDownTimeSec, dIdealCycleTime, dTotalParts, tStart, sShift, dAvailSecFROM dbo.OEEQWorkCellWHERE tStart >= DATEADD(d, - 14, { fn NOW() })) AND (sShift = 'Shift 1' ORsShift = 'Shift 2' ORsShift = 'Shift 3') AND (dAvailSec <> 0)[/code]see this for expplanationhttp://visakhm.blogspot.com/2012/07/generate-datetime-values-from-integers.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
Picassochick
Starting Member
2 Posts |
Posted - 2012-10-26 : 06:00:15
|
Excellent - thanks very much ! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-10-26 : 09:49:39
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|