CODEWITHSUNDEEP
sqlsql-server-2008
SP1
SP1

Reputation: 1202

Update time portion of a datetime in SQL Server

I have a DateTime variable in SQL Server..I have the below code to check for weekend and then subtract the days to make it Friday. Now I also want to set the time of this new Datetime as 5 PM on the Friday. Can someone please tell me how can I achieve this.

if(DATENAME(DW, @EndDate) = 'Sunday')
Begin
Set @EndDate = (SELECT DATEADD(DAY, -2, @EndDate))
End
if(DATENAME(DW, @EndDate) = 'Saturday')
Begin
Set @EndDate = (SELECT DATEADD(DAY, -1, @EndDate))
End
End

Thanks

Upvotes: 0

Views: 73

Answers (2)

vijaykumar .m
vijaykumar .m

Reputation: 1

select cast(CONVERT(varchar(12),GETDATE()) + '17:00' as datetime)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You can add 17 hours:

Set @EndDate = DATEADD(HOUR, 17, DATEADD(DAY, -1, @EndDate))

I have no idea why you have phrased this logic using a subquery. It is not necessary.

If your data already has a time component, then cast to a date first to get rid of the time component, then back to a datetime:

Set @EndDate = DATEADD(HOUR, 17, DATEADD(DAY, -1, CONVERT(datetime, CONVERT(DATE, @EndDate))))

Upvotes: 2

Related Questions