Search code examples
sqlsql-serverssms-2014

get the first n characters of getdate()

I have a case compare date, hour of datetime column and current date,hour

select * from tbl where  LEFT(EVENT_TIME_column,13) !=LEFT(GETDATE(),13)

EVENT_TIME_column format is '2019-08-15 12:32:40.0000000'

when i perform LEFT(GETDATE(),13) result is 'Aug 15 2019'

can you suggest how to get GETDate() in '2019-08-15 12' (date and hour)

Solution

  • If you want the format yyyy-MM-dd hh then can do this:

    SELECT CONVERT(varchar(13),GETDATE(),120);

    db<>fiddle

    You can find a full list of all the style codes for CONVERT in the documentation: Date and Time Styles

    However, it looks like you want to check if the date is within the current hour. That would be:

    WHERE EVENT_TIME_column >= DATEADD(HOUR, DATEDIFF(HOUR, 0,GETDATE()),0)
  AND EVENT_TIME_column < DATEADD(HOUR, DATEDIFF(HOUR, 0,GETDATE())+1, 0)

    This explicitly avoids any functions on the column EVENT_TIME_column; which would make the query non-SARGable.