Insert a datetime value with GetDate() function to a SQL server (2005) table?

Question

I am working (or fixing bugs) on an application which was developed in VS 2005 C#. The application saves data to a SQL server 2005. One of insert SQL statement tries to insert a time-stamp value to a field with GetDate() TSQL function as date time value.

Insert into table1 (field1, ... fieldDt) values ('value1', ... GetDate());

The reason to use GetDate() function is that the SQL server may be at a remove site, and the date time may be in a difference time zone. Therefore, GetDate() will always get a date from the server. As the function can be verified in SQL Management Studio, this is what I get:

SELECT GetDate(), LEN(GetDate());
-- 2010-06-10 14:04:48.293   19

One thing I realize is that the length is not up to the milliseconds, i.e., 19 is actually for '2010-06-10 14:04:48'. Anyway, the issue I have right now is that after the insert, the fieldDt actually has a date time value up to minutes, for example, '2010-06-10 14:04:00'. I am not sure why. I don't have permission to update or change the table with a trigger to update the field.

My question is that how I can use a INSERT T-SQL to add a new row with a date time value ( SQL server's local date time) with a precision up to milliseconds?

Answer 1

Maybe this would work instead of getdate - SYSDATETIME() look here if you can find what you need - http://msdn.microsoft.com/en-us/library/ms188383.aspx

Answer 2

Check your table. My guess is that the FieldDT column has a data type of SmallDateTime which stores date and time, but with a precision to the nearest minute. If my guess is correct, you will not be able to store seconds or milliseconds unless you change the data type of the column.

Answer 3

I would guess that you are not storing the GetDate() value in a DateTime field. If you store the value in a datetime field you will get the maximum precision allowed by the DateTime type. Additionally, DateTime is a binary type (a double actually) so 19 means 19 bytes, not 19 characters.

Try to create a simple table with a Datetime field like this

CREATE TABLE [dbo].[DateTable](
[DateField] [datetime] NOT NULL
)

And add a date with

insert into datetable (datefield) values(getdate())

When you execute a select you will get back a value including milliseconds. The following query

select *  from datetable

returns

2010-06-11 00:38:46.660

Answer 4

As you're on SQL 2005, don't forget the getutcdate() function to ensure that, regardless of where your servers are actually located, you have a constant time reference.

Imagine, you have the server in the UK in winter (i.e. GMT+0), and save a record at 10:30am. You then cut over to a SQL server hosted in California (GMT+8) and 8 hours later save another record.

Using getdate(), both saves record the same time "10:30:00". Using getutcdate(), the first save records at "10:30:00", the second save records "18:30:00".

Not really answering the question, but important in your circumstances.