July 30, 2009 at 12:10 am
In SQL-Server 2008: usenew datatype date
DECLARE @DateOnly DATETIME
SET @DateOnly = CAST(CAST(GetDate() AS Date) AS DATETIME)
SELECT @dateonly
June 14, 2011 at 5:57 am
SELECT CAST(CAST(GETDATE() AS VARCHAR(11)) AS DATETIME) AS Today
June 14, 2011 at 6:22 am
Slightly tangential, but I like to cast as SMALLDATETIME because I usually cut and paste my results into Excel; If I use a datetime, Excel shows 00:00 or somthing but if I use smalldatetime, it shows it as a date without my formatting it.
*******************
What I lack in youth, I make up for in immaturity!
June 14, 2011 at 6:29 am
Also, you can add or subtract days like so:
DECLARE @DateOnly DATETIME
SET @DateOnly = CAST(FLOOR(CAST(GetDate() AS FLOAT)+1) AS smallDATETIME);
print @DateOnly
Prints just like the original did, but a day later. Subtract 2 and you get two days earlier.
Since DateAdd is apparently high overhead, it seems like a good alternative.
*******************
What I lack in youth, I make up for in immaturity!
June 14, 2011 at 6:41 am
Any particular reason to resurrect a discussion that's 3 years old? Or just bored?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 14, 2011 at 6:48 am
It was the "Featured Script" in today's newsletter.
*******************
What I lack in youth, I make up for in immaturity!
June 14, 2011 at 6:50 am
Ah. That's what I get for not reading the newsletter till after work. 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 14, 2011 at 7:18 am
I use these as I figure it's better to use SQL servers internal function than assume Datetime will always be a float:
DECLARE @DateOnly DATETIME
SET @DateOnly = CAST(CONVERT(varchar,GetDate(),112) AS DATETIME)
SELECT @DateOnly
SET @DateOnly = CAST(CONVERT(varchar(10),GetDate(),121) AS DATETIME)
SELECT @DateOnly
June 14, 2011 at 7:26 am
Adding an integer to your code works like DateAdd() as well.
*******************
What I lack in youth, I make up for in immaturity!
June 14, 2011 at 7:55 am
I've done pretty heavy testing on this. The DateAdd-DateDiff method has been the fastest, and has the advantage of being both backwards and forwards compatible.
The Int conversion has rounding issues and simply shouldn't be used.
The float conversion has problems with the new data types in SQL 2008, and thus isn't forwards compatible with that.
The string conversion is a performance killer (takes an average of 4-10 times as long as native date functions).
Conversion to Date is the fastest and is forwards compatible, but isn't backwards compatible.
In situations where backwards compatibility isn't an issue, conversion to Date is best. Where backwards compatibility might matter, the nested date function method is best.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 14, 2011 at 8:34 am
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.
June 14, 2011 at 9:57 am
0.25 should be 6AM, right? Not 4AM.
June 14, 2011 at 9:59 am
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("?
June 17, 2011 at 9:31 am
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!September 1, 2011 at 8:25 am
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."
Viewing 15 posts - 31 through 44 (of 44 total)
You must be logged in to reply to this topic. Login to reply