CODEWITHSUNDEEP
sqlsql-serverdatetime
Tuzki
Tuzki

Reputation: 491

Removing time from datetime in SQL Server

I'm currently trying to remove the time from a datetime.

These are the rows in the table

PK_Date
-----------------------
2011-07-03 00:00:00.000
2011-07-04 00:00:00.000

My code below works, but doesn't actually work. Meaning it doesn't give me an error, but when I look at the results, its still exactly the same. I want to change every single row in that table to become just date eg. 2011-03-04

Any Ideas? Cheers,

 UPDATE dbo.Time 
 SET PK_Date = CONVERT(date,PK_Date,111)  
 FROM dbo.Time

Upvotes: 0

Views: 8996

Answers (2)

Ranadeera Kantirava
Ranadeera Kantirava

Reputation: 324

If just want store date part try altering that column data type and make it as just date

ALTER TABLE dbo.Time ALTER COLUMN PK_Date Date

Upvotes: 1

Vinnie
Vinnie

Reputation: 3929

If your column datatype is DATETIME, you'll always have a time component to the value. If you just want to see the date only, you can either CONVERT it to a string and style it, or CAST or CONVERT it to a DATE

SELECT CAST(PK_Date AS DATE) from dbo.Time
SELECT CONVERT(DATE, PK_Date) from dbo.Time
SELECT CONVERT(VARCHAR(10), PK_Date, 111) from dbo.Time

Upvotes: 1

Related Questions