Snippet: Select a Date or Current Date Without Time

SQL Server 2000

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 Snippet: Select the Time portion from a datetime



Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Snippet: Select a Date or Current Date Without Time | 1 comments | Create New Account
The following comments are owned by whomever posted them. This site is not responsible for what they say.
Snippet: Select a Date or Current Date Without Time
Authored by: Julian Kuiters on Thursday, September 01 2005 @ 08:49 AM EST
It's been suggested elsewhere that this method is faster in large datasets:
SELECT dateadd(d, 0, datediff(d, 0, getdate()))
[ # ]


Gold Coast Aquarium Maintenance | Gold Coast Marine Fish | Gold Coast Tropical Fish
Jewel Jones - Counselling Service - Penrith, Richmond
 
     
 Copyright © 2014 Julian Kuiters
 All trademarks and copyrights on this page are owned by their respective owners.