Get DateTime with time as 23:59:59

user_304767 0
Viewed 77941

I'm trying to do a where statement that specifies a DateTime field is between the start and end of the previous month.

To do this, I need to specify that the first day of the previous month has a time of 00:00:00 and the last day of the previous month has a time of 23:59:59.

This second condition is giving me a headache..

Can someone help me out?

Cheers

MSSQL 2008

date datetime sql
8 Answers

Try this query for using Datetime datatype to get

2018-01-29 23:59:59.997

select dateadd(ms, -3, (dateadd(day, +1, convert(varchar, GETDATE(), 101))))
user_8271870 
declare @myDate DateTime, @lastMonth DateTime,  @thisMonth DateTime
set @myDate = GETDATE()
set @lastMonth = DateAdd(month, -1, CAST(@myDate as Date))
set @thisMonth = DateAdd(day, -DatePart(day, @myDate)+1, CAST(@myDate as Date))

select @myDate as MyDate, DateAdd(day, -DatePart(day, @lastMonth) + 1, @lastMonth) FirstDay, DateAdd(second, -1, @thisMonth) LastDay

Results

user_129197960

Try This:

SELECT dateadd(millisecond,-1,cast(cast(getdate() AS date) AS datetime2))
user_19723330 
SELECT DATEADD(ms, -2,  CAST(CONVERT(date, DATEADD (DAY,1,getdate())) AS varchar(10)))

output: yyyy-mm-dd 23:59:59.997

2020-08-31 23:59:59.997

user_55888160

I hope someone finds this useful

declare  @TodaysDate smalldatetime
declare @TodaysDatepm smalldatetime

First I get the date and Time as of Midnight i.e 16/05/2021 12:00 am

set @TodaysDate = DATEADD(minute, 0,CAST(FLOOR(CAST(CURRENT_TIMESTAMP AS float)) AS datetime))

Then I add 23hours and 59 minutes onto it i.e (60*23)+59 Which gives 1439, from there I use the the dateadd function

set @TodaysDatepm =DATEADD(minute, 1439, @TodaysDate)

This will always print out midnight of what you set in @TodaysDate

Print @TodaysDatepm
user_65046450
Related