Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345

Get DATE part of the DATETIME Expand / Collapse
Author
Message
Posted Tuesday, June 14, 2011 8:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 15, 2011 9:08 AM
Points: 34, Visits: 132
I was using Method 4, saw this post and was an immediate convert to Method 1 till I saw Method 3, the simplest yet, and as it turns out, the fastest!
But nothing beats the simplicity of TRUNC(sysdate) - a PL/SQL function I really miss.
Post #1125061
Posted Tuesday, June 14, 2011 9:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 11:48 AM
Points: 1, Visits: 313
0.25 should be 6AM, right? Not 4AM.
Post #1125135
Posted Tuesday, June 14, 2011 9:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:41 AM
Points: 150, Visits: 595
Our inhouse method of doing this for today's date is:
DATEDIFF(d, 0, GETDATE())
or for a general date from the DB:
DATEDIFF(d, 0, DateField)

DATEDIFF may be more expensive, but is it more expensive than "CAST(FLOOR(CAST("?
Post #1125136
Posted Friday, June 17, 2011 9:31 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:59 AM
Points: 886, Visits: 1,544
For what its worth.... from my 'T-SQL Shortcuts' scripts I found the following:

/*Per SQL GURU Itzah-Ben Gah the fastest way to extract the date portion of a DateTime value is:*/
SELECT CONVERT(DATETIME,Floor(Convert(FLOAT, GetDate() ) ) )


I don't recall if Itzah was referring to a SQL Serve 2XXX specific platform or T-SQL in general and I did not note anything about specific SQL versions along with this shortcut.


Kindest Regards,

Just say No to Facebook!
Post #1127464
Posted Thursday, September 1, 2011 8:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 7, 2014 2:47 PM
Points: 134, Visits: 186
Am I the only one who noticed this error in the original article?

> with right section of the decimal point representing
> number of dates since 01-Jan-1900

"right section of the decimal point" should read either "left of the decimal point" or "whole number portion."




Post #1168701
« Prev Topic | Next Topic »

Add to briefcase «««12345

Permissions Expand / Collapse