Answered by:
How to convert '20140620' to 'DD-MM-YYYY' formate

Question
-
Hi Experts,
I have data for date fields like "20140620"(YYYYMMDD) while inserting into the table it should be in the format "DD-MM-YYYY"
Can any one please help me to achive this.
Thursday, June 12, 2014 8:31 AM
Answers
-
Hello Asma,
You can use the CAST and CONVERT (Transact-SQL) function with Format Parameter 105 = Italian = dd-mm-yyyy
SELECT CONVERT(varchar(10), CONVERT(datetime, '20140620', 112), 105) AS DDMMYYY
Olaf Helper
[ Blog] [ Xing] [ MVP]- Edited by Olaf HelperMVP Thursday, June 12, 2014 8:39 AM
- Marked as answer by Elvis Long Monday, June 23, 2014 2:58 AM
Thursday, June 12, 2014 8:38 AM -
Hi Experts,
I have data for date fields like "20140620"(YYYYMMDD) while inserting into the table it should be in the format "DD-MM-YYYY"
Can any one please help me to achive this.
You do not really want to worry about how or what format the date is storing, you can always do the format while you fetch or at application layer:
create table Test(Co1 date) Insert into test Select '20140620' Select * From test Select CONVERT(varchar(10),co1,105) From test Drop table test
- Marked as answer by Elvis Long Monday, June 23, 2014 2:58 AM
Thursday, June 12, 2014 8:42 AM -
check this:
select format(cast('20140620' as date), 'dd-MM-yyyy', 'en-US'), cast('20140620' as date)
this applies for SQL Server >= 2012
~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011 | My FB Page- Edited by Manoj Pandey (manub22)Microsoft employee Thursday, June 12, 2014 11:01 AM
- Proposed as answer by Uri DimantMVP, Editor Thursday, June 12, 2014 11:47 AM
- Marked as answer by Elvis Long Monday, June 23, 2014 2:58 AM
Thursday, June 12, 2014 11:00 AM
All replies
-
Hello Asma,
You can use the CAST and CONVERT (Transact-SQL) function with Format Parameter 105 = Italian = dd-mm-yyyy
SELECT CONVERT(varchar(10), CONVERT(datetime, '20140620', 112), 105) AS DDMMYYY
Olaf Helper
[ Blog] [ Xing] [ MVP]- Edited by Olaf HelperMVP Thursday, June 12, 2014 8:39 AM
- Marked as answer by Elvis Long Monday, June 23, 2014 2:58 AM
Thursday, June 12, 2014 8:38 AM -
Hi Experts,
I have data for date fields like "20140620"(YYYYMMDD) while inserting into the table it should be in the format "DD-MM-YYYY"
Can any one please help me to achive this.
You do not really want to worry about how or what format the date is storing, you can always do the format while you fetch or at application layer:
create table Test(Co1 date) Insert into test Select '20140620' Select * From test Select CONVERT(varchar(10),co1,105) From test Drop table test
- Marked as answer by Elvis Long Monday, June 23, 2014 2:58 AM
Thursday, June 12, 2014 8:42 AM -
-
check this:
select format(cast('20140620' as date), 'dd-MM-yyyy', 'en-US'), cast('20140620' as date)
this applies for SQL Server >= 2012
~manoj | email: http://scr.im/m22g
http://sqlwithmanoj.wordpress.com
MCCA 2011 | My FB Page- Edited by Manoj Pandey (manub22)Microsoft employee Thursday, June 12, 2014 11:01 AM
- Proposed as answer by Uri DimantMVP, Editor Thursday, June 12, 2014 11:47 AM
- Marked as answer by Elvis Long Monday, June 23, 2014 2:58 AM
Thursday, June 12, 2014 11:00 AM