Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Posts
    107

    Question Unanswered: Equivalent to time datatype in DB2???

    hi friends,

    do anyone know the equivalent datatype in SQL server to 'time' datatype in DB2? For your information, 'time' datatype allows to store only hh:mm:ss.

    Appreciate your help.

    Jake

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

    Re: Equivalent to time datatype in DB2???

    in SQL Server there is no time nor date types
    there's only the datatype DateTime or SmallDateTime

    so to store your time datas

    you can store them with a fake date in DateTime types
    or in a VarChar(8) type

  3. #3
    Join Date
    Feb 2004
    Posts
    107

    Re: Equivalent to time datatype in DB2???

    Originally posted by Karolyn
    in SQL Server there is no time nor date types
    there's only the datatype DateTime or SmallDateTime

    so to store your time datas

    you can store them with a fake date in DateTime types
    or in a VarChar(8) type
    thanks. will they support in the upcoming release 'Yukon'.
    do u have any idea...

    thanks,
    Jake

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

    Re: Equivalent to time datatype in DB2???

    no idea sorry

  5. #5
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    As Yukon is on beta, its very early to say about that and for more information review this Technet site for your doubts.

    HTH
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    The answer is yes...yukon will have a date and time datatype...

    and you can just store the time component in the existing datetime field..it'll just default the date to the first day of the century...which is actually stored internally as 0

    check out:

    DECLARE @x datetime

    SELECT @x = '12:00:00'

    SELECT @x

    And my bigger problem was with the DB@ timestamp


    Code:
    Create Function SQLDateToDB2Date(@TargetDate datetime)
    returns varchar(30)
    as
    begin
    return replace(replace(convert(varchar(30), @TargetDate, 121),':', '.'), ' ', '.')+'000'
    end
    
    SELECT dbo.SQLDateToDB2Date(Getdate())
    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.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Well-well, even 6.5 supports TIME datatype, and as Brett noted, - it'll be 01/01/1900 date portion displayed if you do a select on it.

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by rdjabarov
    Well-well, even 6.5 supports TIME datatype, and as Brett noted, - it'll be 01/01/1900 date portion displayed if you do a select on it.
    huh?

    You don't mean:

    DECLARE @x time

    Do you?

    I guess you could build a user defined datatype...

    But why bother?

    SELECT CONVERT(char(8),Getdate(),108)
    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.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Of course it is DATETIME, but to get just TIME (it doesn't exist as a datatype, but is recognized by many FE's like VB) you do the CONVERT(datetime, '09:00:03.000')

  10. #10
    Join Date
    Feb 2004
    Posts
    107

    Thumbs up

    thanks for the info.

    Jake

    Originally posted by Brett Kaiser
    The answer is yes...yukon will have a date and time datatype...

    and you can just store the time component in the existing datetime field..it'll just default the date to the first day of the century...which is actually stored internally as 0

    check out:

    DECLARE @x datetime

    SELECT @x = '12:00:00'

    SELECT @x

    And my bigger problem was with the DB@ timestamp


    Code:
    Create Function SQLDateToDB2Date(@TargetDate datetime)
    returns varchar(30)
    as
    begin
    return replace(replace(convert(varchar(30), @TargetDate, 121),':', '.'), ' ', '.')+'000'
    end
    
    SELECT dbo.SQLDateToDB2Date(Getdate())

Posting Permissions

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