How to format DateTime column in SQL Server?

SQL Server 2008 onwards you can use the following query

For Date only:

SELECT CONVERT(DATE,GETDATE()) AS 'DateOnly'

For Time only:

SELECT CONVERT(TIME,GETDATE()) AS 'TimeOnly'

Poster, if this helps please login to Mark As Answer. | Alert Moderator

if u want just date without time just write it on a query:

ALTER TABLE table_name
ALTER COLUMN column_name DATE not null

(BZ)

Poster, if this helps please login to Mark As Answer. | Alert Moderator

You can format it in any of the following way as per your need.

SELECT Convert(varchar(11), YourDate, 101)
, FROM YourTable
result: MM/DD/YYYY

SELECT Convert(varchar(11), YourDate, 120)
FROM YourTable
result: YYYY-MM-DD


SELECT Convert(varchar(11), YourDate)
FROM YourTable
result: MMM DD YYYY

Regards,
Raja, USA

Poster, if this helps please login to Mark As Answer. | Alert Moderator

You can extract the date part from the DateTime field of from database tabe using following SQL function:

e.g.:

select convert(varchar, getdate(), 1)

you can change last parameter value as 1,2,3 so on to get different date formats. getdate() function can be replaced by your column name in the sql query

I hope this may help you:

http://programming.top54u.com/post/SQL-Server-2005-Convert-Date-Format.aspx

Poster, if this helps please login to Mark As Answer. | Alert Moderator

Hello,
Since dealing formats of date and time values is very common task, perhaps it is best to use helping applications like described at http://www.kodyaz.com/articles/sql-format-date-format-datetime-t-sql-convert-function.aspx
By the way a convert with style param 23 will solve the problem.

Poster, if this helps please login to Mark As Answer. | Alert Moderator