How to get Only the 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

SQL Server 2000 Forums

SQL Server Development (2000)

How to get Only the Date without time

Author

Topic

CLages
Posting Yak Master

116 Posts

Posted - 2004-08-19 : 08:59:11

How to get Only the Date without time

if I SELECT GetDate(), I get 08-19-2004 08:12:33:12
But I would like to get only 08-19-2004 from a Getdate()
But if i use Substring to get only the piece i want, i have to
convert to char First.
If i Do this , the Date becomes Aug 19 04

How can i get only the date without any time?

Tks
Carlos Lages

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2004-08-19 : 09:03:28

try
select convert(varchar, getdate(),105)

for more conversion u can check this article:
http://www.sqljunkies.com/HowTo/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

He is a fool for five minutes who asks ,
but who does not ask remains a fool for life!

http://www.sqldude.4t.com

Kristen
Test

22859 Posts

Posted - 2004-08-19 : 12:56:15

The most efficient I have found is:

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

This also works for Month / Hour / Minute etc.


SELECT 'Minute', DATEADD(Minute, DATEDIFF(Minute, 0, GetDate()), 0)
UNION ALL
SELECT 'Hour', DATEADD(Hour, DATEDIFF(Hour, 0, GetDate()), 0)
UNION ALL
SELECT 'Day', DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0)
UNION ALL
SELECT 'Week', DATEADD(Week, DATEDIFF(Week, 0, GetDate()), 0)
UNION ALL
SELECT 'Month', DATEADD(Month, DATEDIFF(Month, 0, GetDate()), 0)
UNION ALL
SELECT 'Quarter', DATEADD(Quarter, DATEDIFF(Quarter, 0, GetDate()), 0)
UNION ALL
SELECT 'Year', DATEADD(Year, DATEDIFF(Year, 0, GetDate()), 0)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources