SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 convert to Date only field in a view
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Picassochick
Starting Member

2 Posts

Posted - 10/25/2012 :  11:54:25  Show Profile  Reply with Quote
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, dAvailSec
FROM dbo.OEEQWorkCell
WHERE tStart >= DATEADD(d, - 14, { fn NOW() })) AND (sShift = 'Shift 1' OR
sShift = 'Shift 2' OR
sShift = 'Shift 3') AND (dAvailSec <> 0)

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/25/2012 :  12:14:50  Show Profile  Reply with Quote
See changes in red below
SELECT lOEEWorkCellId,
       sDescription,
       tShift,
       sPartId,
       dRunSec,
       dPartCount,
       dDownTimeSec,
       dIdealCycleTime,
       dTotalParts,
       tStart,
       sShift,
       dAvailSec,
       DATEADD(dd,DATEDIFF(dd,0,tShift),0) AS tShiftDate
FROM   dbo.OEEQWorkCell
WHERE  tStart >= DATEADD(d, - 14, GETDATE()) 
AND (
         sShift = 'Shift 1'
         OR sShift = 'Shift 2'
         OR sShift = 'Shift 3'
     ) AND (dAvailSec <> 0)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/25/2012 :  12:15:20  Show Profile  Reply with Quote

SELECT lOEEWorkCellId, sDescription, tShift,DATEADD(dd,DATEDIFF(dd,0,tShift),0) AS tShiftDateOnly, sPartId, dRunSec, dPartCount, dDownTimeSec, dIdealCycleTime, dTotalParts, tStart, sShift, dAvailSec
FROM dbo.OEEQWorkCell
WHERE tStart >= DATEADD(d, - 14, { fn NOW() })) AND (sShift = 'Shift 1' OR
sShift = 'Shift 2' OR
sShift = 'Shift 3') AND (dAvailSec <> 0)


see this for expplanation

http://visakhm.blogspot.com/2012/07/generate-datetime-values-from-integers.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Picassochick
Starting Member

2 Posts

Posted - 10/26/2012 :  06:00:15  Show Profile  Reply with Quote

Excellent - thanks very much !
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/26/2012 :  09:49:39  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000