Results 1 to 10 of 10

Thread: Date format

  1. #1
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Unhappy Unanswered: Date format

    In SQL-Server 2000, the supported date format is :
    2003-12-30 03:47:34:000

    In DB2 and SQL Anywhere Sybase, the date format is :
    2003-12-30.03.47.34.00000

    I need to import/export data from text files that contain dates in the DB2 format.

    Is there a way to change the supported date format in SQL Server 2000 ?
    Can I change the default format in the DB-Library ?

    No hope but tkx anyway

  2. #2
    Join Date
    Sep 2003
    Location
    Dallas, Texas
    Posts
    311

    Re: Date format

    SQL Server default format is actually 2003-12-30 03:47:34.000

    We import date from other DBMS as string and then convert it to sql datetime format. In your case it would be as of the following,

    select convert(datetime, left(replace(stuff('2003-12-30.03.47.34.00000', 11, 1, ' '), '.', ':'), 19))

  3. #3
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Re: Date format

    well, you didn't quite answer my question
    but it made me think that i could simply
    change the Datetime columns of my tables
    into Varchar types

    Hell with dates !


    Thanks !

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you change your datetime values to varchar, you are just inviting greater problems in the future. Import your data correctly the first time.

    You may need to import it first as a string and then convert it (using joejcheng's formula), but you should store your final values in datetime format.

    blindman

  5. #5
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    The users of my program download files every day and
    the program loads them into the database to update their data.
    These files come from a DB2 database...

    - AND -

    Each time they shut down the program,
    a file is generated with the latest data updated.

    That's why I'm so concerned about the dateformat.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    How does the program load the data? Does it use bulk copy, or does it execute insert statements?

    blindman

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You'll need to convert the unload from DB2 and format on the way out...

    Yes and varchar is fine

    What platform, mainfram, NT, UNIX?


    And is ti fixed width (ah, a mainframe canidate) or csv?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    At this moment we're using win98 platform but
    next year we'll migrate the prog on winXP.

    We use fixed columns files

  9. #9
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    We're on Sybase (SQL Anyware) database but
    next year we're chaging the database to SQL Server
    (boss wants to save money).

    I'm studying the impacts and looking for
    the smoothest way to change the database-progVB
    dialog.

  10. #10
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    That's right !

    The program imports/exports dates from and to DB2.

    So with all you're advices, I'll :

    - declare my tables with Char(26) instead of Datetime or VarChar to be sure that I have fixed columns
    - even maybe create a user define type named DB2_DateTime with rules on data to be stored in those columns
    - use the BlindMan function "CurrentTimestamp" to replace my "Current timestamp" in the old SQL Anywhere queries to store the Now date in the DB2 format
    - use bulk insert to import data from a DB2 file (DB2 dates)
    - maybe use bcp to export data into a file at DB2 destination


    Getting there !
    And they say that USA does'nt like France !

Posting Permissions

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