CODEWITHSUNDEEP
sqlt-sqldateview
KoolKabin
KoolKabin

Reputation: 17643

Extracting yyyy/MM/dd formated date from datetime datatype in T-SQL

I have my date values in postdate column in articles table in sql database table. It stores all date as well as time. I would like to get the date part only. i.e from 2010/07/01 12:45:12 i would likfe to get 2010/07/01 in my view

Upvotes: 1

Views: 1133

Answers (3)

Kashif
Kashif

Reputation: 14430

SELECT convert(varchar, getdate(), 111)

How to format datetime & date in Sql Server 2005

Upvotes: 0

Florian Reischl
Florian Reischl

Reputation: 3856

With SQL Server 2008, you can cast the value as DATE. With previous versions, you can use a trick over DATEADD:

DECLARE @d DATETIME;

SELECT @d = '2010-07-22 12:45:22';

-- all versions of sql server
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, @d), 0);
-- sql server 2008
SELECT CAST(@d AS DATE);

Greets Flo

Upvotes: 6

Shantanu Gupta
Shantanu Gupta

Reputation: 21188

you can use either of the two

select substring(getdate(),0,10)

use 

 select convert(varchar,getdate(),111)

see this for more formats

Upvotes: 1

Related Questions