Current date without the timestamp

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 Additional Articles... » Current date without the timestamp

Current date without the timestamp

Rate Topic

Display Mode

Topic Options

Author

Message

Site Owners

Posted Sunday, September 30, 2007 6:39 AM

SSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,040, Visits: 1

Comments posted to this topic are about the item Current date without the timestamp

Post #404667

CELKO

Posted Friday, October 29, 2010 9:55 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

Use the newer ANSI stuff so you get a real DATE data type and not a DATETIME:

CAST (CURRENT_TIMESTAMP AS DATE)

Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Post #1013184

Michael Valentine Jones

Posted Friday, October 29, 2010 10:13 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 2,969, Visits: 10,616

If you are using DATETIME, these are better ways to get today at 00:00:00.000. The nested DATEADD and DATEDIFF are a more efficient way to do this conversion than conversion to a character string and back again to DATETIME.

Select dateadd(day,datediff(day,0,current_timestamp),0) as CurrDate
Select dateadd(day,datediff(day,0,getdate()),0) as CurrDate

If you are using SQL 2008 or later, then the way Joe Celko suggested will work fine if you want a DATE column.

If you need a DATETIME, this also works in SQL 2008 or later:

select convert(datetime,convert(date,current_timestamp)) as CurrDate

Post #1013199

« Prev Topic | Next Topic »

Permissions