locked
How to convert long date to sql date format RRS feed

  • Question

  • Hi Team,

    Am facing a small issue while converting long date to dd/mm/yyyy. Could you please help on this issue.

    needs to convert 'ddmmyyyyhhmmss' to 'dd/mm/yyyy'. 

    It is bit urgent please help on this issue.

    thanks in advance. 


    Thanks Bala Narasimha

    Thursday, May 2, 2019 7:24 AM

Answers

  • --or this:

    create table test (c1 int, c3 varchar(50)) insert into test values(1,'26112018145900') select Format(Try_PARSE(stuff(stuff(left(c3,8),3,0,'/'),6,0,'/') as datetime using 'en-GB'),'dd/MM/yyyy') c3 from test

    --or

    select 
     Format(datefromparts(substring(c3,5,4),substring(c3,3,2),substring(c3,1,2) ) ,'dd/MM/yyyy') c3
    from test

    drop table test



    Thursday, May 2, 2019 1:36 PM

All replies

  • SELECT FORMAT(getdate(), 'dd/MM/yyyy')

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, May 2, 2019 7:48 AM
    Answerer
  • Hi BaluChalla,

     

    Please try following script.

     
    declare @currenttime datetime ='02/05/2016 03:04:23:000'
    ----1
    SELECT convert(varchar(20),@currenttime,103) as result 
    ----2
    SELECT FORMAT(@currenttime, 'dd/MM/yyyy')as result 

     

    If above sample doesn’t satisfy your requirement, please share us your table structure and some sample data along with your expected result. So that we’ll get a right direction and make some test.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 2, 2019 8:09 AM
  • Thanks for your reply.

    for example:



    Thanks Bala Narasimha

    Thursday, May 2, 2019 10:04 AM
  • DECLARE @Date bigINT = 20221107235959


    SELECT FORMAT(Cast(LEFT(@Date,8) as date), 'dd/MM/yyyy')

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, May 2, 2019 10:21 AM
    Answerer
  • create table test (c1 int, c3 varchar(50))
    insert into test values(1,'26112018145900')
    
    select  
    Convert(varchar(10),Cast(substring(c3,5,4)+substring(c3,3,2)+substring(c3,1,2)  as datetime) ,103) c3
    from test
    
    
    drop table test

    Thursday, May 2, 2019 1:23 PM
  • --or this:

    create table test (c1 int, c3 varchar(50)) insert into test values(1,'26112018145900') select Format(Try_PARSE(stuff(stuff(left(c3,8),3,0,'/'),6,0,'/') as datetime using 'en-GB'),'dd/MM/yyyy') c3 from test

    --or

    select 
     Format(datefromparts(substring(c3,5,4),substring(c3,3,2),substring(c3,1,2) ) ,'dd/MM/yyyy') c3
    from test

    drop table test



    Thursday, May 2, 2019 1:36 PM