Re: date format

Tiffany,

There are multiple ways to do this conversion, typically using various date functions. If the range of possibilites is somewhat limited (a few dozen), some think that the Update/CASE statement that I sent you may be the easiest for to understand and work with.

While the option below may seem a bit more refined, it requires with certainity that certain patterns are consistant. The Update/CASE just doesn't change the exception rows, leaving them for you to find and replair.

Another option is:

UPDATE MyTable
SET ColumnToUpdate = REPLACE(ColumnToUpdate, '6-', '2006 ') + ' 01'
WHERE ColumnToUpdate LIKE '6-%'

And a similar statement for 2005, 2004, etc. (Note the 'blanks' in the line above.)

--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam


"Tiffany" <Tiffany@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:24844F2F-AF0C-4FFD-B357-E2B5ED029694@xxxxxxxxxxxxxxxx
Hi Arnie,

Thanks for your suggestions, however the csv file is extremely large and is
impossible to load the entire database in excel application. Hence, I have to
convert it in sql. Any solution available?



"Arnie Rowland" wrote:

If this is a one time issue, and depending upon the filesize, the easiest
way to handle this just might be to load the file into MS Excel and use the
Edit| Find/Replace capabilitiy to transform the data, save the file as a
csv, and then load it into SQL Server using DTS or however you were planning
to do so.

For example, with the following Replace steps, set the 'By Column' search
criteria, replace 6-mar with 3/1/2006.

Obviously, there will be a few repeated steps to cover all dates, but it may
be quicker than trying to find a way to code SQL to do it. (Yes, it is
possible, but it may not be worth the time and effort.)

Regards,

--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam


"Tiffany" <Tiffany@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E2B0CE2B-C4BF-40CA-A095-D685D780819C@xxxxxxxxxxxxxxxx
Hi,

I received a csv file which has a date column. The date format in this
column is 6-Mar (6 stands for 2006 which means Mar 2006). I will import
this
data to an sql table and i want to date to look like 3/1/2006. Is it
possible. Kindly advise.

Thank you


Relevant Pages

  • Re: date format
    ... If this is a one time issue, and depending upon the filesize, the easiest ... and then load it into SQL Server using DTS or however you were planning ... be quicker than trying to find a way to code SQL to do it. ... I received a csv file which has a date column. ...
    (microsoft.public.sqlserver.mseq)
  • Re: Importing DBF into SQL CE
    ... Load the data in to dataset using OpenNET CSV adapter and ... insert it from there in to SQL CE. ... how you import the CSV file? ...
    (microsoft.public.sqlserver.ce)
  • Re: transfer data into an external database
    ... >With regards to your SQL code I have no idea about SQL. ... If there is a schema.ini in the same folder as the CSV file, ... >> Create a Schema.ini file based on an existing table in your database: ... John Nurick ...
    (microsoft.public.access.externaldata)
  • RE: read csv file to sql
    ... OdbcConnection conn = new OdbcConnection; ... So now what code do I use to get the dataset into an exsisting SQL table. ... The primary field of the SQL table is not in the CSV file so I can't just ... We can add a new ODBC data source under ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Import csv Updte and delete
    ... In article, Gustavo wrote: ... > beautiful SQL 2000. ... > Every week I have a csv file with over 7,500,000 records ... > 2-Update the existing records and append the new ones. ...
    (microsoft.public.sqlserver.dts)