Retrive Date in YYYYMMDD format in SQL [Resolved]

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

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

0	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

0	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

0	Thanks a Lot Saravanan ... your logic works !!!! Cheers, Rahul Gosavi Rahuljgosavi, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts