How to Select Current Date Without Time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to Select Current Date Without Time

Author

Topic

cnbhold
Starting Member

43 Posts

Posted - 2010-11-17 : 15:49:37

In the following Select statement, how do I create the DateStamp column without the time?

Currently, I'm getting: 11/17/2010 2:49:58 PM

Select GetDate() as DateStamp, FirstName, LastName
From SomeTable

Angel

Kristen
Test

22859 Posts

Posted - 2010-11-17 : 15:54:28

SELECT DateAdd(Day, DATEDIFF(Day, 0, GetDate()), 0)

will chop off the time from a date - GetDate() in this example.

If using SQL2008 then

SELECT CONVERT(DATE, GetDate())

will also chop off the time.

If you just want to change the display format of the date you can use

CONVERT(char(999), GetDate(), 9999)

were 999 is the width of your output, and 9999 is the code for the format you want (look up the values in DOCs)

But ... this converts the date from DATE or DATETIME datatype to Char, and the receiving application will get a Char rather than a Date, so any Date manipulation that the Application does will involve converting it back from Char to Date - and possibly using an unexpected conversion format in the process ... leading to bugs.

So it is generally better to handle Date formatting in the application itself.

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2010-11-19 : 00:31:00

select convert(varchar,getdate(),101)

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-11-19 : 00:37:59

quote:
Originally posted by ahmeds08

select convert(varchar,getdate(),101)

Remember the datatype is no more datetime!

Please specify size for varchar!

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

Refer!

http://beyondrelational.com/blogs/madhivanan/archive/2007/12/04/column-length-and-data-length.aspx

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-19 : 03:22:12

quote:
Originally posted by cnbhold

In the following Select statement, how do I create the DateStamp column without the time?

Currently, I'm getting: 11/17/2010 2:49:58 PM

Select GetDate() as DateStamp, FirstName, LastName
From SomeTable

Angel

It matters only if you want to show them somewhere. If you use front end application, do formation there

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources