Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2009
    Posts
    79

    Unanswered: A query to change date format.

    Hi All,

    Can we have a query in SQL server 2005 which will change the date format of all the records of a particular column in a sql database table. for e.g. I have database table name mydata and I have a column in the same database table .i.e. Created On Date but the dates in that column are like this "20100630" and I want to change the format to something like this "06/30/2010" If yes, can any one provide me an example for the same.

    Thanks a lot for your help in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Please show the CREATE TABLE statement, five rows of data from the table, and the output that you'd like based on the rows that you show.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2009
    Posts
    79

    A query to change date format.

    Hi,

    Actually I am importing data from a text file into excel and then I am cleaning it up in excel especially date column .i.e. created on date column and then I am importing that excel file data in my sql server database table using import export wizard. What I want to do is to skip the excel step and directly import text file into sql server database table and then run some query to cleanup the date column.

    for e.g. when I import data from text file to excel I get the column Created on Date and in that column the dates reflect in the following way .i.e. "20100630" but I want to put the date into this format .i.e. "06/30/2010".

    Thanks a lot for your help in advance.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    In your first post you stated specifically that you wanted to change data in an sql database table.
    In your second post you state you want to change data before it is loaded into the table.
    And you have completely ignored Pat's simple request for details.

    Do you really expect anybody to spend their time helping you with this if you can't formulate your question or answer pertinent questions?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jul 2010
    Posts
    8
    Why not just work on the excel file and skip the txt file instead? you said you got the formatting done there in excel.

  6. #6
    Join Date
    Sep 2009
    Posts
    79

    A query to change date format.

    Hi All,

    As mentioned earlier, I get the data in text files and I need to upload the same in an sql server database table but don't directly upload it to sql server because I want to change the date format in the data which comes to me in text files and that's why I use excel in between .i.e. I import the text file data into excel first and then using the some formulas I change the format of the date column and then I upload it to sql database server.

    Now my question is if I directly upload the flat file .i.e. the text file into my sql server database table and then run some query which will change the date format of all records of the date column.

    I get the column Created on Date and in that column the dates reflect in the following way .i.e. "20100630" but I want to put the date into this format .i.e. "06/30/2010".

    Please let me know if we can have query which will put the date in my required format after I import the data from text to sql server database table.

    Thanks a lot for your help in advance.

  7. #7
    Join Date
    Sep 2009
    Posts
    79

    A query to change date format.

    Hi All,

    Did anyone get the chance to look into my above post. Please help...

    Thanks a lot for your help in advance.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The characters used to format a date for display are a presentation issue, not a database issue. The database actually stores the date in a way that is easy for programs to manipulate, but doesn't make much if any sense to people...

    When you retrieve the date from the database, it comes from the server to the client as a swarm of bits that are nonsensical to the human eye. The client software then picks an appropriate way to display that swarm of bits, and the client software is what normally determines how the date is formatted.

    It is possible for the database to convert the date to characters, which means that the database can then determine how the date is formatted when the date is retrieved (probably via a SELECT statement) but this is a notoriously bad idea. Let the client format the data, don't cripple your application by forcing the database to handle formatting and locale specific issues.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    More on Pat's answer. It is not the answer you wanted, but it is the correct one.
    How to format a Date or DateTime in SQL Server
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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