November 6, 2002 at 4:51 pm
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?
November 6, 2002 at 5:17 pm
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).
November 7, 2002 at 2:18 am
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
======================
November 7, 2002 at 4:13 am
But that only applies to if the variable remians in scope.
November 7, 2002 at 8:05 am
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
November 7, 2002 at 9:11 am
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)
November 7, 2002 at 4:06 pm
can i just make parse out the date, and not worry about the time? that makes sense i think.
November 7, 2002 at 4:35 pm
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.
November 8, 2002 at 2:10 am
The CONVERT function is the way I do would do it in production.
Nigel Moore
======================
November 8, 2002 at 11:49 am
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