March 30, 2010 at 8:58 pm
Hi
I am using sql server 2005 and i would like reterive date only from the GetDate(). Please let me know how can i do this
For example: 2010-03-25 13:55:43.547
the required is only should be 2010-03-25
Please suggest
Thanks
March 30, 2010 at 9:31 pm
select convert(varchar,getdate(),111)
Regards,
Raj
March 31, 2010 at 1:57 am
As per required output -
select REPLACE( convert(varchar,getdate(),111), '/', '-')
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
March 31, 2010 at 2:38 am
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS DT
If you do a quick search you should be able to find a few sites with lists of the different date formats using CONVERT.
Have a quick look over: http://www.sql-server-helper.com/tips/date-formats.aspx
March 31, 2010 at 3:04 am
Dohsan (3/31/2010)
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS DT
If you do a quick search you should be able to find a few sites with lists of the different date formats using CONVERT.
Have a quick look over: http://www.sql-server-helper.com/tips/date-formats.aspx
Thanks for this information.
Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together
March 31, 2010 at 4:34 am
Also asked here
I answered
SELECT CAST(GETDATE() AS DATE)
N 56°04'39.16"
E 12°55'05.25"
April 1, 2010 at 7:08 am
If I'm not mistaken, SELECT CAST(GETDATE() AS DATE) will only work on SS2008 and above, as the DATE type is new in 2008.
*******************
What I lack in youth, I make up for in immaturity!
April 1, 2010 at 8:22 am
If there are points available for being obscure...
SELECT {fn CURRENT_DATE()};
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 3, 2010 at 7:16 am
select cast(getdate() as date) doesn't work on SQL Server 2005
April 3, 2010 at 7:25 am
richellere (4/3/2010)
select cast(getdate() as date) doesn't work on SQL Server 2005
Bross mentioned that. 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 3, 2010 at 11:57 am
Paul White NZ (4/1/2010)
If there are points available for being obscure...
SELECT {fn CURRENT_DATE()};
:w00t: huh? :hehe: (ODBC yeah?)
---------------------------------------------------------------------------------
April 3, 2010 at 12:06 pm
There is a very long set of posts titled "Just the date, please"
http://qa.sqlservercentral.com/Forums/Topic379596-8-2.aspx
The below method is 10 times faster than converting to character and then truncating the time portion:
CAST(convert(int, convert(float, getdate() )) AS DATETIME)
Alternatively using all casts instead of converts:
CAST( CAST ( CAST getdate() as float ) as integer ) AS DATETIME)
SQL = Scarcely Qualifies as a Language
April 3, 2010 at 12:25 pm
Nabha (4/3/2010)
Paul White NZ (4/1/2010)
If there are points available for being obscure...
SELECT {fn CURRENT_DATE()};
:w00t: huh? :hehe: (ODBC yeah?)
Yeah - works in normal Transact-SQL too though!
Try it!
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 3, 2010 at 12:40 pm
Carl Federl (4/3/2010)
CAST(convert(int, convert(float, getdate() )) AS DATETIME)
Few of issues with this one.
1. Relies on undocumented internal implementation details.
2. Only works with DATETIME - none of the new 2008 types.
3. Cannot be persisted as a computed column (in versions before SQL Server 2008).
The DATEADD/DATEDIFF method is my preference FWIW.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 3, 2010 at 1:09 pm
Paul White NZ (4/3/2010)
Carl Federl (4/3/2010)
CAST(convert(int, convert(float, getdate() )) AS DATETIME)Few of issues with this one.
1. Relies on undocumented internal implementation details.
2. Only works with DATETIME - none of the new 2008 types.
3. Cannot be persisted as a computed column (in versions before SQL Server 2008).
The DATEADD/DATEDIFF method is my preference FWIW.
Don't forget what happens to dates with time portion between 12:00 and 24:00 (pm times)...
N 56°04'39.16"
E 12°55'05.25"
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply