date format converting - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

date format converting

Author

Topic

thepul
Starting Member

1 Post

Posted - 2011-09-28 : 11:58:50

hi, i have a DATE in a column in this format 2006/06/08 12:00:00 AM
and i need to convert this format to this format 2006/06/08

can anyone pls tell me the correct SQL statement to convert this date format ? i dont need to creat another column and want to convrt sate existing date data in the same column.
thepul2006@yahoo.com
thanks & cheers

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-28 : 12:02:22

provide data type is datetime it will always have a time part. you shouldnt be worried about it so long as its 12:00 AM (start of day). You can retrieve it in format you wanted by using the format functions in front end or another way is to use CONVERT() in t-sql

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vmvadivel
Yak Posting Veteran

69 Posts

Posted - 2011-09-29 : 01:07:54

As Visakh has pointed out above you can make use of CONVERT function to retreive the DATE portion alone if you need to!

--Replace Getdate() with your date column
SELECT CONVERT(VARCHAR(10), getdate(), 101)
SELECT CONVERT(VARCHAR(10), getdate(), 102)
SELECT CONVERT(VARCHAR(10), getdate(), 103)
SELECT CONVERT(VARCHAR(10), getdate(), 104)
SELECT CONVERT(VARCHAR(10), getdate(), 105)
SELECT CONVERT(VARCHAR(10), getdate(), 106)
SELECT CONVERT(VARCHAR(10), getdate(), 107)
SELECT CONVERT(VARCHAR(10), getdate(), 110)
SELECT CONVERT(VARCHAR(10), getdate(), 111)
SELECT CONVERT(VARCHAR(10), getdate(), 112)

Best Regards
Vadivel

http://vadivel.blogspot.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-29 : 08:09:00

I would suggest doing it using CONVERT only if you've no ways of achieving it in front end though. This is a formatting requirement and has to be dealt with at front end as far as possible.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources