Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

SSIS - SQL Convert YYYY-MM-DD to DD-MM-YYYY

Options
  • 12-03-2018 5:00pm
    #1
    Diceicle
    Registered Users Posts: 1,799 ✭✭✭


    Hello,

    Does anyone have a solution for displaying Dates in the UK style date format while maintaining it as a Date Data-type?

    Flat file data comes in as 'Mar 12 2018 15:59' - I would like this to be displayed as '12-03-2018' and as a Date Data-type in order to do further time-based queries down the line.

    Thanks


Comments

  • Options
    #2
    BrokenArrows
    Registered Users Posts: 7,501 ✭✭✭BrokenArrows


    Diceicle wrote: »
    Hello,

    Does anyone have a solution for displaying Dates in the UK style date format while maintaining it as a Date Data-type?

    Flat file data comes in as 'Mar 12 2018 15:59' - I would like this to be displayed as '12-03-2018' and as a Date Data-type in order to do further time-based queries down the line.

    Thanks

    You need to use convert. This doesnt change the column type, you are just changing the way it is displayed for this query.
    See here for all the conversion settings.
    https://dba.stackexchange.com/questions/66837/change-datetime-format

    You want to use format 103

    SELECT convert(nvarchar(MAX), MyDateColumn, 103)


  • Options
    #3
    Pelvis
    Closed Accounts Posts: 1,758 ✭✭✭Pelvis


    If it's in a flat file as 'Mar 12 2018 15:59' then it's likely being imported into the table as a string of text rather than a date data type? If so a convert won't have any affect.


  • Options
    #4
    Diceicle
    Registered Users Posts: 1,799 ✭✭✭Diceicle


    Pelvis wrote: »
    If it's in a flat file as 'Mar 12 2018 15:59' then it's likely being imported into the table as a string of text rather than a date data type? If so a convert won't have any affect.

    Its coming in in a Flat File but it has a script task run on it to convert to a numerical date sequence - initially its imported as a varchar then converted to a date-type for writing to the DB.


  • Options
    #5
    daymobrew
    Registered Users Posts: 6,501 ✭✭✭daymobrew


    Diceicle wrote: »
    Its coming in in a Flat File but it has a script task run on it to convert to a numerical date sequence - initially its imported as a varchar then converted to a date-type for writing to the DB.
    Would it be possible to change the script task to convert the date string into a more flexible format?
    It would be a one-off task to convert existing db entries and the knock on effects on existing query scripts would have to be investigated.


Advertisement