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

    Unanswered: User defined function

    I'm trying to create this function to get the current Date-Time
    and format it in the DB2 format

    create function CurrentTimestamp()
    returns varchar as
    begin
    declare @Now varchar
    select @Now=cast(getdate() as varchar)
    select @Now=left(@Now,4) + '-' +
    substring(@Now,6,2) + '-' +
    substring(@Now,9,2) + '.' +
    substring(@Now,12,2) + '.' +
    substring(@Now,15,2) + '.' +
    substring(@Now,18,2) + '.' +
    substring(@Now,21,3)
    return(@Now)
    end

    But getdate() doesn't work in functions,
    how then do I retrieve the current Date-Time ?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Create Function SQLDateToDB2Date(@TargetDate datetime)
    returns varchar(30)
    as
    begin
    return replace(replace(convert(varchar(30), @TargetDate, 121),':', '.'), ' ', '.')
    end
    go

    print dbo.SQLDateTODB2Date(getdate())
    go

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    First I think it's 3 dashes and three dots

    Try this:

    SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1


    Also the micro seconds are 6 positions

    so you need:

    Code:
    USE Northwind
    GO
    
    Create Function SQLDateToDB2Date(@TargetDate datetime)
    returns varchar(30)
    as
    begin
    return replace(replace(convert(varchar(30), @TargetDate, 121),':', '.'), ' ', '.')+'000'
    end
    go
    
    print dbo.SQLDateTODB2Date(getdate())
    go
    
    DROP FUNCTION SQLDateToDB2Date
    GO
    But how how handy blindman....definetly will come in handy..

    However, and here's the rub, I bet Karolyn is importing as varchar the DB2 date as is (which is always 26 chars)

    So we need to truncate it with something like LEFT(col1,23) then add the +'000'..

    But the Korolyn, if you have a synch up process you'd be SOL...
    Last edited by Brett Kaiser; 11-05-03 at 15:43.
    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.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't know DB2, so I just formatted the result as was demonstrated in Karolyn's other post.

    "However, and here's the rub, I bet Karolyn is importing as varchar the DB2 date as is (which is always 26 chars)"
    I think this is the opposite of her other post. I think she is trying to go from SQL Server to DB2 in this instance, while in her other open thread she is trying to convert DB2 to SQL Server format.

    I see your point about adding the '000'.. I wondered about that too, but not being a DB2 person, I don't know how picky it would be about such things.

    Mostly I wanted to show her the concept of a general conversion function, versus her attempt at returning only the current datetime.

    blindman

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yea,,,I said it would be useful...

    DB2 picky? OMG? Very!

    And yes...Coming or Going?

    But the converse could made as well, and then you could support either direction...

    but not being a DB2 person....
    another thing to be grateful for.....
    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
    Nov 2003
    Location
    FRANCE
    Posts
    393
    Thx Mister Grand Poobah.
    @TargetDate that's what I was looking for !

    Discovering the functions of SQL Server.
    Not a bad database after all !

    I hope I'll have better perfs with this database
    (than SQL Anywhere)

  7. #7
    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 !

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    [man falling dead on floor]
    SQL Anywhere
    [/man falling dead on floor]


    AHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHHhh

    Be afraid....be very afraid...................
    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
  •