How to format DateTime column in SQL Server?

Posted by Poster under Sql Server on 12/9/2008 | Views : 64692 | Status : [Member] | Replies : 5
I am trying to select DateTime field from database and I am getting DateTime field value as YYYY-MM-DD 00:00:00.000, how to get only Date part not the time and other parts?




Responses

Posted by: Kundan64 on: 1/2/2013 [Member] Starter | Points: 25

Up
0
Down
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

Posted by: Beskito on: 9/9/2013 [Member] Starter | Points: 25

Up
0
Down

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

Posted by: Raja on: 12/9/2008 [Member] Starter

Up
0
Down
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

Posted by: Coolcode on: 12/30/2008 [Member] Starter

Up
0
Down
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

Posted by: SqlDeveloper on: 1/12/2010 [Member] Starter

Up
0
Down
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

Login to post response