asked on

Microsoft SQL -- getdate(YYYY-MM-DD) ?

Below works.

How can I change the below 2013-08-26 15:30:00.000 to something like
getdate(YYYY-MM-DD) 15:30:00.000 since I always want it to
displays today at 3:30pm ?
---------------------------------------------------------------------------
UPDATE TIME_SCANNING
SET Status = 'STOP',
TotalTime =
CASE
WHEN (DATEPART(HOUR, DateTime) < 11) OR
(DATEPART(HOUR, DateTime) = 11 AND DATEPART(MINUTE, DateTime) < 15)
THEN (cast(cast(datediff(minute, DateTime , '2013-08-26 15:30:00.000' )as decimal(30,4)) / 60 as decimal(30,2)) - .5)/ Qty
ELSE
cast(cast(datediff(minute, DateTime , '2013-08-26 15:30:00.000' )as decimal(30,4)) / 60 as decimal(30,2)) / Qty
END

SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

DcpKing

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ralmada

try this way:

dateadd(d, 0, datediff(d, 0, yourcolumn)) + '15:30:00'

example:

declare @t datetime
set @t = '2013-08-26 11:30:00.000' 
select dateadd(d, 0, datediff(d, 0, @t)) + '15:30:00'

Open in new window

finance_teacher

ASKER

How can I change the below #1 to something like
"getdate(),126)' 15:30:00.000" since I always
want today @ 3:30pm ?

UPDATE TIME_SCANNING
SET Status = 'STOP',
TotalTime =
CASE
WHEN (DATEPART(HOUR, DateTime) < 11) OR
(DATEPART(HOUR, DateTime) = 11 AND DATEPART(MINUTE, DateTime) < 15)
--#1 below works and is what I want --> making "TotalTime=6.15" which is correct, but I want 2013-08-26 to be today's date
--THEN (cast(cast(datediff(minute, DateTime , '2013-08-26 15:30:00.000' )as decimal(30,4)) / 60 as decimal(30,2)) - .5)/ Qty

--#2 below works but is not what I want --> making "TotalTime=14.65" since it goes to end-of-today instead of just 3:30pm today
THEN (cast(cast(datediff(minute, DateTime , CONVERT(char(10), getdate(),126))as decimal(30,4)) / 60 as decimal(30,2)) - .5)/ Qty

END

ASKER CERTIFIED SOLUTION

ralmada

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial