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 Thursday, July 30, 2009 12:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 17, 2014 6:00 AM
Points: 22, Visits: 42
In SQL-Server 2008: usenew datatype date

DECLARE @DateOnly DATETIME
SET @DateOnly = CAST(CAST(GetDate() AS Date) AS DATETIME)
SELECT @dateonly
Post #762169
Posted Tuesday, June 14, 2011 5:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 19, 2012 12:32 PM
Points: 1, Visits: 24
SELECT CAST(CAST(GETDATE() AS VARCHAR(11)) AS DATETIME) AS Today
Post #1124894
Posted Tuesday, June 14, 2011 6:22 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 13, 2013 10:26 AM
Points: 67, Visits: 215
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!
Post #1124917
Posted Tuesday, June 14, 2011 6:29 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 13, 2013 10:26 AM
Points: 67, Visits: 215
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!
Post #1124923
Posted Tuesday, June 14, 2011 6:41 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1124931
Posted Tuesday, June 14, 2011 6:48 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 13, 2013 10:26 AM
Points: 67, Visits: 215
It was the "Featured Script" in today's newsletter.

*******************
What I lack in youth, I make up for in immaturity!
Post #1124935
Posted Tuesday, June 14, 2011 6:50 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1124938
Posted Tuesday, June 14, 2011 7:18 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 3:19 AM
Points: 338, Visits: 1,421
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

Post #1124975
Posted Tuesday, June 14, 2011 7:26 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 13, 2013 10:26 AM
Points: 67, Visits: 215
Adding an integer to your code works like DateAdd() as well.

*******************
What I lack in youth, I make up for in immaturity!
Post #1124980
Posted Tuesday, June 14, 2011 7:55 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, June 27, 2014 12:43 PM
Points: 15,444, Visits: 9,596
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
Post #1125011
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse