Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393

    Unanswered: Putting dates into varchar

    I've got 2 tables :

    TABLE DateSqlServer
    Date as DateTime

    TABLE DateDb2
    Date as VarChar(26)

    I run these queries :

    INSERT INTO DateSqlServer (Date) VALUES (GetDate())
    INSERT INTO DateDb2 SELECT Date FROM DateSqlServer

    Then, when I run :

    SELECT Date FROM DateDb2

    I get
    "march 8 2004 3:45 PM"

    instead of
    "2004-03-08 03:45:12:000"

    How can I transfer the date as I see it in table DateSQLServer
    WITHOUT doing FORMATs on the Date column ?

    Why does the INSERT transform the date format ?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It's an implicit conversion..you need to CONVERT it into the format you want.

    Dates are stored as 8 position numerc and SQL does the conversion that way...

    It's another SQL Server "feature"

    In my Own Opinion (MOO) it should fail.

    Can you jam a varchar in to an int? No

    Try

    INSERT INTO DateDb2 (you really should always list the columns here)
    SELECT CONVERT(varchar(26), [Date], 120)
    FROM DateSqlServer

    MOO
    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.

  3. #3
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    I wanted to avoid using list of columns

    I'm loading my SQL-Server database from the linked DB2 database
    with a unique distributed query (good for all tables)


    for each table (Table1, Table2, Table3, ..)

    INSERT INTO [SQL-Server-DB].[TableX]
    SELECT *
    FROM OPENQUERY ([DB2-Server],
    'SELECT DB2-DB.TableX.* FROM DB2-DB.TableX')


    This way I did'nt need the structure of each table
    AND the datatype of each column
    to load the data

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    I would recommend a different approach ....
    Build a repository table and store name of tables and fields in that ...
    build dynamic queries ( yeah, I know that is bad) for getting the data from the db2 tables with whatever conversions you need
    Get yourself a copy of the The Holy Book

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

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    oye...

    Where to start...

    First, open a BIG bottle of Tequila...

    Next, I'm big on the dynamic code generation...saves lots of time...

    But not on a nightly process.

    Yo uneed to know the structures and they need to be static.

    Otherwise you're looking for trouble.

    And while you're looking, it'll find you.

    I would set up unloads of the data on DB2...ftp/copy it to your server and bcp

    You can write format card generators...gen ftps, gen bcps...everything...

    But then that's it...you have to then test it all, then install it....

    As static sprocs...

    Don't forget error handling...

    What's the whole reason for this?

    Oh, and MOO
    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.

  6. #6
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    I am using this type of proc for import from SAP where the table structure remains constant ..
    Get yourself a copy of the The Holy Book

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

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well if it's constant, why would you need dynamic anything (except to gen the statement in the first place?)
    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.

Posting Permissions

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