Vikram Kamath

October 27, 2009

DateTime Manipulation: Date Part or Time Part

Filed under: SQL Server — Vikram @ 10:53

There are times when using SQL server 2000 or 2005 , you really want to use either the date-part or the time-part of a datetime value. But, you would still want to use it as a datetime value for comparison. Usually, as a solution, you would try and build a new datetime by converting the current datetime into a string or extracting the day, month, year etc. from the datetime using the respective SQL functions.

The SQL server’s default language settings may sometimes poses problems when building the new datetime using various parts of the datetime.

However, I found the following solution from here. It is a clean solution to convert the datetime to midnight or keep the time-part but change the date-part to 1900-01-01.

Here is the code:
SELECT GETDATE() AS [CURRENT_DATE],
       CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) AS [DATE_PART],
       CAST(CAST(GETDATE() AS FLOAT) - FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) AS [TIME_PART]

Advertisements

2 Comments »

  1. For most up-to-date news you have to pay a quick visit internet and on internet I
    found this site as a most excellent website for latest
    updates.

    Comment by camera lenses for canon — July 3, 2013 @ 22:30

  2. There are specific breeds lower susceptible to chocolate
    however never givve your dog chocolate by choice.
    They may also tump to get attention, which might mean they want out of the cage to
    play and be with the family. Findd Indian astrology wioth
    horoscope and live astrological prediction , aries
    horoscope, daily, monthly, yeaarly analysis oof birth chart and individual prediction off all planet with gemstone.

    Comment by Kaitlyn — January 13, 2014 @ 10:51


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: