Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2003
    Posts
    111

    Unanswered: Simple Question About Data Type

    Hi all

    I always assumed that the length of datatype specified by "Books Online" is counted by the number of character (e.g datatime type and varchar). Is this true?

    or does the length of datatypes specified the number bytes it can hold??

    Cheers

    J

  2. #2
    Join Date
    Aug 2003
    Posts
    111
    I should mention that
    the datatype that i am really interested in is the datatime datatype with length of 8. when i look into the database through enterprise manager, the values that gets returned has much more characters than just 8. from this observation, i am thinking the datetime data must be stored in a different format than chars and the length of the data type would probably be refering to a length of 8 bytes??

    i don't know, just need someone to verify this.

    cheers

    James

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you are correct, datetimes are stored "in a different format than chars"

    datetimes are stored as two 4-byte integers, one for the number of days since the base date (jan 1 1700 or something, i forget, it's in Books OnLine somewhere), and the other for the number of seconds (or is it milliseconds?) since midnight

    any time you "see" a date, you can be assured that those two numbers have been converted, because you never get to see the numbers themselves, do you?



    rudy
    http://r937.com/

  4. #4
    Join Date
    Aug 2003
    Posts
    111
    Thank you

    Originally posted by r937
    you are correct, datetimes are stored "in a different format than chars"

    datetimes are stored as two 4-byte integers, one for the number of days since the base date (jan 1 1700 or something, i forget, it's in Books OnLine somewhere), and the other for the number of seconds (or is it milliseconds?) since midnight

    any time you "see" a date, you can be assured that those two numbers have been converted, because you never get to see the numbers themselves, do you?



    rudy
    http://r937.com/

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    r937,

    I think that SQL Server's datetime storage algorythm is a little more complex than that. MS Access and Excel date values work that way, but I got burned once when I tried truncating datetime values to date-only in SQL server by using the INT function to get the date-only part. I found that the integer value returned after Noon was 1 greater than the value before Noon.

    Have you ever run into this, or do you know anything that would shed more light on it?

    blindman

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

    noon? verrrrrry interrrrrresting...

    interesting idea --
    the integer value returned after Noon was 1 greater than the value before Noon
    this suggests quite strongly that there's an astronomical Julian Day algorithm involved

    anyhow, "truncating" (you must've used oracle at some point in your life) a sql server datetime value to get rid of the time component is usually done this way --

    cast(convert(char(10),datetimefield,120) as datetime)


    rudy

  7. #7
    Join Date
    Aug 2003
    Posts
    111

    Re: noon? verrrrrry interrrrrresting...

    what is the difference between CAST and CONVERT
    don't they basically do the same thing? Details please.

    Originally posted by r937
    interesting idea --this suggests quite strongly that there's an astronomical Julian Day algorithm involved

    anyhow, "truncating" (you must've used oracle at some point in your life) a sql server datetime value to get rid of the time component is usually done this way --

    cast(convert(char(10),datetimefield,120) as datetime)


    rudy
    Can you explain the TSQL line quoted above, why is cast and convert used in that order.

    I can see why CONVERT might be necessary in cases where the datetime object needs to be displayed in different format by using the style field. But is that the only reason for both CONVERT and CAST to exist. I also read somewhere that CAST should be the preferred command.

    Cheers

    James

  8. #8
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: noon? verrrrrry interrrrrresting...

    Originally posted by nano_electronix
    what is the difference between CAST and CONVERT
    don't they basically do the same thing? Details please.
    Looking up CAST and CONVERT in T-SQL syntax, you will see that both functions do basically the same. CONVERT() adds some formating functionality for converting to or from datetime.

    Originally posted by nano_electronix
    Can you explain the TSQL line quoted above, why is cast and convert used in that order.
    In every computer language, expressions are performed from the inside to the outside. So, play parser by yourself, look which expressions are parts of other expressions, and see step by step what happens if you evaluate the basic expressions first.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, CAST is preferred, as it is the standard function, whereas CONVERT is a microsoft extension to the standard

    however, CAST cannot do the date formatting that CONVERT was built for

    as DoktorBlue suggested, if you start on the "inside" of the expression, CONVERT will make a char(10) string of format style 120 out of the datetime value

    the first 10 characters of a style 120 string are yyyy-mm-dd, so the string will be the date without the time in ISO (internationl standard) format

    then CAST converts that string back to datetime


    much better, of course, is not to have to do this at all -- design your application to store datetime values without a time component, i.e. with a time component that defaults to midnight, in those cases where a time component doesn't make sense (e.g. DateOfBirth)


    rudy

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    r937,

    I've used cast(convert(char(10),datetimefield,120) as datetime) many times, but its odd that this double conversion and the manipulation of a character string is required me Microsoft's SQL Server data logic, when all other Microsoft products allow the simple int(DateTimeValue). I can only think this oddity is left over from SQL Server's Sybase history.

    blindman

  11. #11
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Originally posted by blindman
    all other Microsoft products allow the simple int(DateTimeValue)
    Which Microsoft products do you mean?
    MS Access: int(#23-8-2002 11:43:00#) = 37491
    MS Excel: does not know int()

    And why to blame Sybase? I can't see the relation.

    But the issue is to get the date out of a datetime. Rudy already suggested the most simple solution: not to store the time part at all. If, however, there is a time part to remove, there are several ways. Besides to remove the time as shown by Rudy, you may also get the year(), month() and day() part, and pass it to a user-defined function. Or without any string operations:

    DateAdd(dd, DateDiff(dd, '1900-01-01', <YourDateTimeColumn>), '1900-01-01')

    So, also your third point is weak, but I admit that SQL Server isn't known for it's rich function set.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Doktorblue,

    Not sure what version of Excel you are using. 1.1? Excel has had an int() function for a long time.

    In Excel and Access, taking the integer portion of datetime values yields equivalent results for all values on the same day. Same for Access. Taking the integer portion of a datetime value in SQL Server yields different results depending on whether your value is before 12:00 noon or after 12:00 noon (or at least has for me on several platforms). I hope you understand, because I'm not sure I can explain it any more simply.

    If you do some reading about Microsoft SQL Server's development history, you'll see that the engine was a spinoff from Sybase. I believe they were partners, and then had a falling out. I suspect that SQL Server has retained the datetime properties of Sybase, which were developed independently of other Microsoft products. If you want to learn more about SQL Server, do some research on the internet. There is a wealth of information out there that can help you.

    blindman

  13. #13
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447
    Originally posted by blindman
    If you want to learn more about SQL Server, do some research on the internet. There is a wealth of information out there that can help you.
    Thanks, grand-pa, for sharing your wisdom with me. Did you ever see a university from the inside? To give you a hand, I have this terrific source of information for you: research. If you would mean, what you said, you would have provided at least a reference, but not such a bullshit remark.

    I'm glad for you, that you have some vague believes about the common root of Sybase and sQL Server, too. To turn your believe into knowledge (follow my link and understand the relation between research and knowledge), sit back and listen, what a doc has to tell:

    Once upon a time, in the late 1980s and early 1990s, the Sybase RDBMS (Relation Database Management Systems) was one of the most popular and innovative RDBMS systems. RDBMS systems could be found in UNIX and Netware environments. The entry fee to purchase a RDBMS system was not within the reach of many small businesses and workgroups.

    To this end, Sybase and Microsoft entered into a joint venture. Microsoft would license and sell the Sybase RDBMS system, SQL Server, under the Microsoft name on the OS/2 platform. Microsoft SQL Server for OS/2 became a good affordable workgroup RDBMS system. The product, however, was limited by OS/2 in its scability and performance.

    The relationship between Microsoft and Sybase became strained and ended around the time Microsoft announced that they had rewritten SQL Server for the Windows NT platform. The Windows NT platform is Microsoft's operating system of the future, slated to take on the UNIX and NetWare operating systems. Due to pricing and performance, Microsoft SQL Server for Windows NT quickly became one of the most popular Windows NT applications.

    The split between Sybase and Microsoft is quite apparent when looking at the two SQL Server products. Microsoft SQL Server is tightly integrated into the NT operating system and the database administration task are packaged into several graphical front-end tools. Administering Microsoft SQL Server for Windows NT is not the same as administering a Sybase SQL Server lacking the graphically administrating and scheduling tools.

    So, since Microsoft rewrote the software, there isn't any reason to keep their function library identical to Sybase.

    The function, you mean in Excel, listens to the name "Integer()", and returns indeed the date partion. Int() in Access, however, returns the number of days since 1/1/1900. So, to transform that number into the corresponding date, you can use two, semantically identical, notations:

    DateAdd("y", 0, int(tDateTimeValue))
    or
    DateAdd("y", int(tDateTimeValue), #1900-01-01#)

    However, just using int() doesn't do the job in Access. Actually, I gave you the last transformation, but written with SQL Server syntax, which does not work with string manipulations, or with an int() implementation, depending on whether the timestamp is before noon or after. I recommand to use it.
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Doktorblew,

    I see you have learned how to use a browser and are working on your cut-and-paste skills. Both are valuable tools that will server you well as your career progresses.

    The split between Microsoft and Sybase is quite apparent now, but several versions ago they were virtually identical. Enterprise manager is not truly a part of the SQL Server Engine, but is merely an (admittedly powerful) tool that Microsoft has developed to distinguish itself from Sybase. It is still possible, and sometimes advantageous, to administer SQL server without ever using Enterprise Manager.

    The big leap for Microsoft was switching to the use of files rather than devices, making administration much easier. Last I heard, Sybase still uses devices, but I haven't worked with Sybase in about three years.

    And of course, there was a big reason to retain parts of SQL Server's Sybase heritage. It's called "backward compatibility", and its important to those of us who create databases designed to last more than six months.

    I don't see you mention anywhere why SQL Server's datetime format is still different from other MS products. A pity, because that was what my original question was about, and would have been the only thing in your post that I didn't already know.

    By the way, like much of what I've seen you post, your code is needlessly bloated, in kind of a Rube Goldberg fashion. In Access, you can just take the integer portion of the datetime value and assign it directly to a datetime variable, or function. DateAdd("y", 0, int(tDateTimeValue)) is unnecessarily verbose, and DateAdd("y", int(tDateTimeValue), #1900-01-01#) is outright hilarious.

    ...and one more thing, Excel doesn't "listen" to anything without speech recognition software, and "Integer()" is NOT a valid Excel function unless you are trying to return the value "#NAME?". The correct syntax is "Int()", as a stated before.


    blindman

    P.S. You get funny when you're angry!
    Last edited by blindman; 08-30-03 at 19:36.

Posting Permissions

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