Results 1 to 3 of 3

Thread: Date conversion

  1. #1
    Join Date
    Nov 2002
    Location
    cornwall
    Posts
    187

    Date conversion

    Hi all

    I wonder whether any of you can help me with a bit of code that you may have already had to execute??

    I have a SQL database logging activities and a load of information in a mdb file that needs to be imported.

    Unfortunately the data in the SQL database is in the format yyyy-mm-dd and the data in the mdb file is in dd/mmmm/yyyy.

    When i run a DTS to import the data the new rows are imported as they were YYYY-dd-mm.

    example:
    data logging as
    2003-08-10
    2003-08-11

    imported data from last week arrives as
    2003-01-08
    2003-02-08
    2003-03-08
    etc

    how can i manipulate the data in SQL to reverse the day and month numbers for Aug 1st to Aug 8th??

    I have tried changing the mdb data format but that doesnt make a difference. I dont understand DTS enough to know whether it is possible there and my SQL skills dont rise to the challenge - yet!!

    TIA

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    I don't understand how 2003-08-11 gets transformed into 2003-02-08, it should have been 2003-11-08 if it is because of dd/mm and mm/dd difference.

    Anyway, you can modify the transformation properties and use vbscript to transform date as you want it sql server.

  3. #3
    Join Date
    Nov 2002
    Location
    cornwall
    Posts
    187
    sorry, poor explanation.

    The current logging is entering the database as 2003-08-11 etc.

    The historical data that i need to import is being imported as 2003-01-08.

    The point i was trying to illustrate is that the month position in the date contruct is being switched

    The two data sets are exclusive so one day isnt being turned into another, the numbers that comprise the date are being transposed to make it represent a different date.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •