What's With GetDate() ?

  • I have a procedure which populates a field in a table with GetDate().

    I just ran the procedure, and predictably each appropriate record now shows in this field a value of '2002-11-06 15:34:16.030'.

    Then I ran a query which searches for any record which shows GetDate() in this field, and I come up with nothing.

    Then, if I run:

    print GetDate()

    I get 'Nov 6 2002 3:44PM'.

    Is the fact that what 'Print GetDate()' returns, and what GetDate() populates a field with are different the reason why my query comes up empty? And if so, how can that be rectified?

  • No GetDate() is a funtion that returns the server time at the time it is run. So you run it say at 11:15 am exactly today you get

    2002-11-06 11:15:00.000

    but if you run 10 minutes and 30 secounds later it will be

    2002.11.06 11:25:30.000

    the .000 is the number of millisecounds of the time I just choose .000 as I didn't want to get to expressive here.

    Print is actually outputing the same information but when the database stores a datatime value the field default return format of the stored data is yyyy-mm-dd 24hour:minutes:secounds.millisecounds where prints is what you see (I believe under the hood is actually returning the number of millisecounds since 1/1/1970 midnight).

  • Assign getdate to a variable.

    Use the variable in your update.

    Use the same varibale in your select, you should get the rows returned.

    Nigel Moore
    ======================

  • But that only applies to if the variable remians in scope.

  • I think the problem is a matter of precision. From the SQL BOL :

    "datetime

    Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table."

    "smalldatetime

    Date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute. smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute."

    You are thinking that because GETDATE() returns the same minute in the stored procedure that it will return all of the records that match from the previous insert. The problem is there are some milliseconds that have elapsed between your insert and your select. If they are in the same scope, you can do as nmoore said below, and even if not in the same scope pass the date variable into whatever other procedure / function you would happen to be calling. Or you could use the smalldatetime for the column if you do not care about the loss of precision. It would allow you to select by using minutes only. Or you use BETWEEN to select from '2002-11-06 15:34:16.000' AND '2002-11-06 15:34:16.999'. You choose. My preference is option 1.

    Tim C.

    //Will write code for food

    Edited by - tcartwright@thesystemshop.com on 11/07/2002 08:38:31 AM


    Tim C //Will code for food

  • Another solution would be to use DateDiff with the column and getdate(). Specify Day, Hour, minute, etc according to how precise of a match you care about. Then return records that have a difference of less than 1.


    "I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
    (Memoirs of a geek)

  • can i just make parse out the date, and not worry about the time? that makes sense i think.

  • Actally it is a bit easier to use CONVERT(char,GETDATE(),101) as will output as mm/dd/yyyy. See BOL for more detail on CONVERT and other options available.

  • The CONVERT function is the way I do would do it in production.

    Nigel Moore
    ======================

  • I think the issue with GETDATE() is one of those screwy Microsoft things....through experimenting with it I came to this conclusion:

    When used with SELECT, GETDATE() brings back the system date/time as:

    yyyy-mm-dd hh:mi:ss:mmm(24hour)

    BUT when GETDATE() is stored, PRINTed, etc., it returns the date/time as per the format SQL Server is set to use. So my SQL Server is using the DEFAULT, so it returns:

    mon dd yyyy hh:miAM (or PM)

    For someone with the SQL Server set to use British/French it will return dd/mm/yy.

    You can 'force' it to return something else by using CONVERT, but otherwise it seems to do a hidden CONVERT from system time to whatever your SQL Server is set to.

    -Bill

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply