Function to Return Current Date

Question

Post reply

Function to Return Current Date

guerillaunit

SSCarpal Tunnel

Points: 4249
More actions
January 23, 2012 at 2:09 pm

#250018

Hi there, I am looking for a function that will return today's date without the time. Date() does this in Access, what is the equivalent of date() in SQL Server? Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jared SSC Guru Points: 61793 More actions · Answer 1

Try this...

SELECT CAST(CAST(GETDATE() AS DATE) AS DATETIME)

This will 0 out the date. If you want the actual date only, then just do this:

SELECT CAST(GETDATE() AS DATE)

NOTE: This will not work on 2005

Jared
CE - Microsoft

GSquared SSC Guru Points: 260824 More actions · Answer 2

SELECT CONVERT(DATE, CURRENT_TIMESTAMP)

- 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

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Or just use the date datatype.

declare @MyDate date = CURRENT_TIMESTAMP

select @MyDate

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

or the following

declare @Date date = GETDATE()

select @Date

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

guerillaunit SSCarpal Tunnel Points: 4249 More actions · Answer 5

Is there a way to get the numeric value of the date only (with 0:00 time) and use it in a subtraction?

This is what I have right now, but I get the error "Explicit conversion from data type date to numeric is not allowed."

(CONVERT(numeric, CONVERT(date, current_timestamp), 101))-(CONVERT(numeric, abb.[due date], 101))

LutzM SSC Guru Points: 107049 More actions · Answer 6

Why don't you use the DATEDIFF() function?

What's the goal you're trying to achieve?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

What is your end goal?

What should the data look like after performing this calculation?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

LutzM (1/23/2012)
Why don't you use the DATEDIFF() function?
What's the goal you're trying to achieve?

That is funny - same question (goal), same time - different sources.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

LutzM SSC Guru Points: 107049 More actions · Answer 9

SQLRNNR (1/23/2012)
LutzM (1/23/2012)
Why don't you use the DATEDIFF() function?
What's the goal you're trying to achieve?
That is funny - same question (goal), same time - different sources.

If I wouldn't be involved, I'd say something along "Great minds think alike"...

But now all I can say about my post is "Every dog has its day." 😀

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

LutzM (1/23/2012)
SQLRNNR (1/23/2012)
LutzM (1/23/2012)
Why don't you use the DATEDIFF() function?
What's the goal you're trying to achieve?
That is funny - same question (goal), same time - different sources.
If I wouldn't be involved, I'd say something along "Great minds think alike"...
But now all I can say about my post is "Every dog has its day." 😀

Looks like my day was today. 😉

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

guerillaunit SSCarpal Tunnel Points: 4249 More actions · Answer 11

I am looking to get an integer (the number of days between the 2 dates)

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

Just use datediff for that. http://msdn.microsoft.com/en-us/library/ms189794.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/