Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2003
    Posts
    39

    Unanswered: Saving/Storing Time only in Datetime

    Hello all,

    Perhaps someone out there could enlighten me on the issue of date/time (particularly time) storage.

    I am trying to get the time component to be stored in a Datetime field. I can do a Date only as follows:

    INSERT INTO Test ([DateTime])VALUES(Cast(convert(char(10),GETDATE(),102) as Datetime))

    I attempted the following:


    INSERT INTO Test ([DateTime])VALUES(Cast(convert(char(8),GETDATE(),108) as Datetime))

    But I cannot get the time component without the default date 1/1/1900 component.

    If any of you gurus out there can help me out it would be very much appreciated.

    Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    It isn't possible to store a date or a time by itself, however you can format the character result as date or time or both. Just ignore the date if it isn't relevant to your needs.

    -PatP

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, yes but you can store the datetime value with zero as the integer (date) portion. It's equivalent to storing it as a particular time on January 1st, 1900 (or whatever...). I've seen a lot of applications do this, but as Pat pointed out it is usually unnecessary.

    Since databases are about storing information, why do you want to discard the date portion of your time value?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2003
    Posts
    39
    Alot of our data is "date" only type information. Having just the date only makes queries and coding easier etc... I was toying with the idea of just storing time separately in the few instances that I need it. But I see now that I would have to store it as a different data type or as you stated ignore the default date of 1/1/1900.

    Thanks for the insight.

    RLM

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    my 2 (where is that cents char?)

    If you don't go with datetime, then you'll miss out on all the power of the date functions...

    DATEADD, DATEDIFF, DATEPART, ect....
    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
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Brett

    Select char(162)


  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    OK then...

    That was my SELECT '2'+ char(162)


    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
    Posts
    39
    Yeah I know. The time component can be textual for most cases. It is usually for display only(if I need to search the time component I will save it in a datetime field and ignore date part in the rare cases I care about time).

    There is a lot of old code (Access and VB) here that saves the date only (Access auto-magically does this for us through bound forms) that the end users use. If a time component is in the data it will confuse users (no extra help in that department req'd.).

    Until I get (full) control of the old apps here and get my database standardized I need to maintain the original format.

    I have written several SP's and DTS packages that replace old Access front end functions (my slow but sure moving of business rules into the backend) so therefore my date formatting issues have cropped up. The Access queries do not work right with the Time component in most cases. The SQL code puts the default time in. So rather than try to fix old apps to work with the issue (which are already slated for rewrites) easier to just coerce the data for now.

    Someday it will all be good! Job security right?

    Sorry for the extra verbage...caught me on a chatty day. Decaf tomorrow!


    RLM

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    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.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Actually, Access stores time data as a full datetime value, with the integer portion equal to zero. It is equivalent to storing SQL Server times with a date of 1/1/1900.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Nov 2003
    Posts
    39

    Talking

    I'm doin alright! I guess I'll pass on the decaf tomorrow.

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    While it is a bit of a pain in the patoot, you can add a trigger to forceably rip the time off (which cleans it up with no code changes required), or a constraint that will never allow the time in the first place (which forces code to get the value right in the first place).

    Yeah Brett, but this incarnation of the Yak Corral is WAY down from the original. We've got a LONG way to go to get anywhere close to the levels that inspired!

    -PatP

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by Pat Phelan
    . We've got a LONG way to go to get anywhere close to the levels that inspired!

    -PatP
    Sure enough....but we're closing in 100 again.....

    http://www.dbforums.com/showthread.p...21#post3651321

    Code:
    USE Northwind
    GO
    
    CREATE FUNCTION udf_Time(@x varchar(25))
    RETURNS char(8)
    AS
    BEGIN
    DECLARE @y char(8)
    
    SELECT @y = CONVERT(char(8),CONVERT(datetime,@x),108)
     WHERE ISDATE(@x) = 1
    
    RETURN @y
    END
    GO
    
    SELECT dbo.udf_Time(GetDate())
    
    SELECT dbo.udf_Time('Brett')
    
    DROP FUNCTION udf_Time
    You could do the same for Dates I imagine
    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.

  14. #14
    Join Date
    Nov 2003
    Posts
    39

    Thumbs up

    I'll give it a shot!



    RLM

Posting Permissions

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