Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Current date without the timestamp Expand / Collapse
Author
Message
Posted Sunday, September 30, 2007 6:39 AM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, Visits: 1
Comments posted to this topic are about the item Current date without the timestamp
Post #404667
Posted Friday, October 29, 2010 9:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

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
Posted Friday, October 29, 2010 10:13 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:11 PM
Points: 3,081, Visits: 11,230
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 »

Add to briefcase

Permissions Expand / Collapse