Get DATE part of the DATETIME

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jano Petras » Get DATE part of the DATETIME

45 posts, Page 2 of 5

««1 2 3 4 5 »»»

Get DATE part of the DATETIME

Rate Topic

Display Mode

Topic Options

Author

Message

ChrisM@Work

Posted Tuesday, February 05, 2008 7:11 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 5,705, Visits: 11,140

DECLARE @TheDate DATETIME
SET @TheDate = '2008-02-05 23:59:59.997'
SELECT @TheDate
SELECT CAST(CAST(@TheDate-0.5000000385803 AS INT) AS DATETIME)
SET @TheDate = '2008-02-05 00:00:00.000'
SELECT CAST(CAST(@TheDate-0.5000000385803 AS INT) AS DATETIME)
;)

We're still on 2K, could be decades before we see 2K8 Sick

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps

Post #451621

bc_

Posted Tuesday, February 05, 2008 9:00 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, June 10, 2013 9:15 AM
Points: 1,304, Visits: 7,125

it doesn't wait until noon to start rounding up...

DECLARE @TheDate DATETIME

SET @TheDate = '2008-02-05 11:59:59.993'
SELECT @TheDate
SELECT CAST(@TheDate AS INT)
SELECT CAST(CAST(@TheDate AS INT) AS DATETIME)

SET @TheDate = '2008-02-05 11:59:59.997'
SELECT @TheDate
SELECT CAST(@TheDate AS INT)
SELECT CAST(CAST(@TheDate AS INT) AS DATETIME)

bc

Post #451699

ChrisM@Work

Posted Tuesday, February 05, 2008 9:25 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 5,705, Visits: 11,140

DECLARE @TheDate DATETIME, @HalfDayAndaBit DECIMAL (14,13)
SET @HalfDayAndaBit = 0.5000000385803

SET @TheDate = '2008-02-05 11:59:59.993'
SELECT @TheDate
SELECT CAST(@TheDate-@HalfDayAndaBit AS INT)
SELECT CAST(CAST(@TheDate-@HalfDayAndaBit AS INT) AS DATETIME)

SET @TheDate = '2008-02-05 11:59:59.997'
SELECT @TheDate
SELECT CAST(@TheDate-@HalfDayAndaBit AS INT)
SELECT CAST(CAST(@TheDate-@HalfDayAndaBit AS INT) AS DATETIME)

That's where the 'AndaBit' comes in.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

Post #451719

Ryan Davidson

Posted Tuesday, February 05, 2008 9:59 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, July 02, 2009 3:20 PM
Points: 1, Visits: 9

If you don't care about the date coming back as an string. Then this is good solution too.

PRINT LEFT(getdate(), 11)

Post #451741

dbajunior

Posted Tuesday, February 05, 2008 2:24 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:36 PM
Points: 122, Visits: 1,009

or...

Select convert(char(10),getdate(),101)

Post #451899

Julie Breutzmann

Posted Tuesday, February 05, 2008 2:59 PM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 12:34 PM
Points: 814, Visits: 1,604

Thanks for a helpful article. Good discussion, too!

Julie

Post #451913

Derek Dongray

Posted Wednesday, February 06, 2008 2:56 AM

Ten Centuries

Group: General Forum Members
Last Login: Monday, May 13, 2013 2:04 AM
Points: 1,342, Visits: 1,946

James A. Lawrence (2/5/2008)

or...

Select convert(char(10),getdate(),101)

Of course I'd use convert(char(10),getdate(),103)...

Which raises a question I haven't found an answer to:

Why does America (and nowhere else) write dates in their traditional order, i.e. month/day/year?

Everywhere else has a date order which either goes from specific to general (day-month-year) or the reverse (y-m-d). Why does America use a medium-small-large (m-d-y) ordering such that a mechanical 'odometer' would have to update the middle section first, then the left end, then the right end rather than the more logical left-to-right or right-to-left?

Derek

Post #452082

jcrawf02

Posted Wednesday, February 06, 2008 7:09 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:46 PM
Points: 2,561, Visits: 18,910

why am I getting times in the result for all of these, when I want only the date portion?

---------------------------------------------------------
How best to post your question
How to post performance problems
Tally Table:What it is and how it replaces a loop

"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

Post #452180

Sean Walker

Posted Wednesday, February 06, 2008 7:13 AM

SSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 6:10 AM
Points: 630, Visits: 285

That'd be because the data type is dateTIME.
If you want just date, convert to SQL 2K8 and use a date only datatype, or as previous example above have done, convert to a char data type with only the date portion being returned.

Post #452182

ChrisM@Work

Posted Wednesday, February 06, 2008 7:16 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 5,705, Visits: 11,140

jcrawf02 (2/6/2008)

why am I getting times in the result for all of these, when I want only the date portion?

Curious - can you post what you've tried, with the results?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

Post #452185

« Prev Topic | Next Topic »

45 posts, Page 2 of 5

««1 2 3 4 5 »»»

Permissions