Often you’ll want to compare datetime values based on the date only, ignoring the time. This is especially important when doing date ranges, where thinking like a human you might want ‘Select Accounts Join Offers Where SignupDate between 1st Jan and 31st Jan’. As a human we’re probably thinking ‘inclusive’. As a computer, SQL Server is thinking a SignupDate like 31st Jan @ 01:00:00am is outside that range. Damn computers and their exactness.
Anyway, here’s how to get just the date part out of a DateTime or SmallDateTime data type:
— Get current date (without time) as string:
SELECT CONVERT( CHAR(8), GetDate(), 112)
— Get current date (without time) as date:
SELECT CAST( CONVERT( CHAR(8), GetDate(), 112) AS DATETIME)
Of course, in place of GetDate() you could put a column name.
Using the CONVERT( … , … , style ) function we can specify the style attribute, that lets us specify to SQL Server (using style 112) that we want the ISO standard date format of yyyymmdd. SQL Server is then able to reliably convert that back into a DateTime data type, regardless of your regional settings. This is really important if you are a user that doesn’t live in the USA, as your regional settings are probably not set to mmddyyyy.
Of course if you want just the time portion without the date, see [story:t-sql-time-without-date]