Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    2

    Unanswered: conversion of date format

    Hello,

    I have a huge tab-separated text file (~1GB) which I import into SQL.

    One of the columns of this file represents date, but it is not in SQL format: the date is specified as "28jan2001", whereas in SQL format it should be "2001-01-28".

    Is there any simple way to read this field so that it will be in SQL format.
    Thanks.

    Best wishes,
    Alexander

  2. #2
    Join Date
    Jan 2003
    Posts
    15
    Yes, I think you can specify how the date should be formatted when entered in SQL. Not sure of the syntax, but it can be entered as dd/mm/yyyy.

  3. #3
    Join Date
    Jan 2003
    Location
    Vietnam
    Posts
    188

    Re: conversion of date format

    Originally posted by statnikov
    Hello,

    I have a huge tab-separated text file (~1GB) which I import into SQL.

    One of the columns of this file represents date, but it is not in SQL format: the date is specified as "28jan2001", whereas in SQL format it should be "2001-01-28".

    Is there any simple way to read this field so that it will be in SQL format.
    Thanks.

    Best wishes,
    Alexander
    Try this:
    1- Import the text file into a temp mysql table where the date column format is CHARACTER(10)
    2- create another table with the same columns as the temp table except the date column format is now DATE
    3- use search and replace to change "jan" into "-01-" so that you have "28-01-2001" in that text column
    4- now is the time you copy all record from temp table to the new one, you probably know how to do this correctly....

    Good luck
    qha_vn

Posting Permissions

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