-1

I trying to fetch data for the latest full half hour as per the clock time.

for example if getdate() return me '2016-11-14 10:14:25.567' , i want data from '2016-11-14 09:30:00.000' to '2016-11-14 10:00:00.000'

Bob Gilmore
  • 12,608
  • 13
  • 46
  • 53
user6363065
  • 81
  • 2
  • 10
  • 2
    dateadd(minute, -30, getdate())? – James Z Nov 14 '16 at 16:22
  • So you want it to show the half hour (as per clock time) in which the getdate() falls? – Rich Benner Nov 14 '16 at 16:22
  • 1
    `Where yourcolumn >= dateadd(minute, -30, getdate())` – Pரதீப் Nov 14 '16 at 16:23
  • 1
    The way i read the question is that he doesn't want just the latest 30 minutes, he wants the latest full half hour as per the clock time. – Rich Benner Nov 14 '16 at 16:28
  • @Rich - yes I agree - it is incorrectly assigned as duplicate - the question is much trickier than the one classed as duplicate - ~A golden rule has been broken here - because he DID give us an exact example of what he wanted – Cato Nov 14 '16 at 16:38
  • how about 'datediff(minute,0,getdate()) / 30 = datediff(minute,0,YourField) / 30' – Cato Nov 14 '16 at 16:40

2 Answers2

2

I think you want something like this;

DECLARE @DateVariable datetime; SET @DateVariable = GETDATE()

SELECT
    @DateVariable Current_DateTime
    ,DATEADD(mi,-30, CONVERT(smalldatetime, ROUND(CAST(@DateVariable AS float) * 48.0,0,1)/48.0) ) From_Time
    ,CONVERT(smalldatetime, ROUND(CAST(@DateVariable AS float) * 48.0,0,1)/48.0) To_Time

Where the date variable is now (2016-11-14 16:26:52) it will give you the last full half hour

The result will look like this;

Current_DateTime            From_Time               To_Time
2016-11-14 16:26:52.073     2016-11-14 15:30:00     2016-11-14 16:00:00
Rich Benner
  • 7,873
  • 9
  • 33
  • 39
0

Use DateADD(mi, -30, getdate())

Nirjhar Vermani
  • 1,215
  • 8
  • 17