#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    2
    Rep Power
    0

    SQL Dateformat reverse


    Hi,

    Dunno if I am in the right forum or not.

    For school project we have a database the date format is dd-mm-yyyy

    We need to import this database in a program that uses phpmyadmin but the data format in that database is yyyy-mm-dd

    I found this:

    Code:
    declare @dates table (orig varchar(50) ,parsed datetime)
    
    SET DATEFORMAT ydm;
    
    insert into @dates
    select '2008-09-01','2008-09-01'
    
    SET DATEFORMAT ymd;
    insert into @dates
    select '2008-09-01','2008-09-01'
    
    select * from @dates
    Source: http://stackoverflow.com/questions/331002/change-default-date-time-format-on-a-single-database-in-sql-server

    But I don't really understand it.

    I also tryed making the database just the yyy-mm-dd format but that ends op with 0000-00-00 in the records.

    My teacher also said something about the left(), mid(), right() functions, I looked it up and readed that's used for showing parts not reverse inputting them?

    So my question is:

    How can i reverse the dateformat from dd-mm-yyyy to yyyy-mm-dd withouth ending op with a 0000-00-00 but with 1953-12-23 for example the original date in the database
  2. #2
  3. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7170
    My teacher also said something about the left(), mid(), right() functions, I looked it up and readed that's used for showing parts not reverse inputting them?
    Your teacher is actually right. Your goal is not to reverse the whole string, you're just trying to reorder subparts of it; in order to do that, you need to fetch the subparts (year, month and day).

    For example, LEFT(date_column, 2) would return 'dd' from a 'dd-mm-yyyy' string. Once you have the parts, LEFT for dd, MID for mm, RIGHT for yyyy, then when you do an insert you can simply reorder them: RIGHT MID LEFT. You'll probably need to perform concatenation of the parts as well. In MySQL that's done using the CONCAT function.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    2
    Rep Power
    0
    edit:

    nvm just used the functions wrong

    Thnxs for the help and with the CONCAT function also worked for me too

IMN logo majestic logo threadwatch logo seochat tools logo