Retrive Date in YYYYMMDD format in SQL [Resolved]

Posted by Rahuljgosavi under Sql Server on 3/17/2011 | Points: 10 | Views : 9097 | Status : [Member] | Replies : 4
Hi All,

I have one varchar field in sql table which is storing the date in ddmmyyyy format e.g 17032011. While reading I need
to read this field in YYYYMMDD format i.e20110317 ?

Regards,
Rahul Gosavi




Responses

Posted by: T.saravanan on: 3/17/2011 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
Hi,

Actually you have value in 'Varchar' type (ddMMyyyy).So there is no possibility directly convert to datetime.
Try this...Here i am using 'substring' for achieve your output..

DECLARE @sDate varchar(10)
SET @sDate = '17032011'
SELECT substring(@sDate, 5, 4) + substring(@sDate, 3, 2) + substring(@sDate, 1, 2)

Output: 20110317


Thanks,
T.Saravanan

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

Posted by: Gsolvers on: 3/17/2011 [Member] Starter | Points: 25

Up
0
Down
Try this

SELECT CONVERT(varchar(10), GETDATE(), 103 ) AS MyDate;


You can change the 3rd parameter to any other number for you required format.


Best Regards,

VG
www.TeacherJi.com

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

Posted by: Madhuk on: 3/17/2011 [Member] Starter | Points: 25

Up
0
Down
This gives your required format
SELECT CONVERT(varchar(10), GETDATE(), 120 ) AS MyDate; 


Check for all formats.
http://www.sql-server-helper.com/tips/date-formats.aspx

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

Posted by: Rahuljgosavi on: 3/17/2011 [Member] Starter | Points: 25

Up
0
Down
Thanks a Lot Saravanan ... your logic works !!!!

Cheers,
Rahul Gosavi

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

Login to post response