Asked  7 Months ago    Answers:  5   Viewed   53 times

Which one:

  • datetime
  • datetime2

is the recommended way to store date and time in SQL Server 2008+?

I'm aware of differences in precision (and storage space probably), but ignoring those for now, is there a best practice document on when to use what, or maybe we should just use datetime2 only?

 Answers

14

The MSDN documentation for datetime recommends using datetime2. Here is their recommendation:

Use the time, date, datetime2 and datetimeoffset data types for new work. These types align with the SQL Standard. They are more portable. time, datetime2 and datetimeoffset provide more seconds precision. datetimeoffset provides time zone support for globally deployed applications.

datetime2 has larger date range, a larger default fractional precision, and optional user-specified precision. Also depending on the user-specified precision it may use less storage.

Tuesday, June 1, 2021
 
Bere
answered 7 Months ago
35

you should put those two dates between single quotes like..

select Date, TotalAllowance from Calculation where EmployeeId = 1
             and Date between '2011/02/25' and '2011/02/27'

or can use

select Date, TotalAllowance from Calculation where EmployeeId = 1
             and Date >= '2011/02/25' and Date <= '2011/02/27'

keep in mind that the first date is inclusive, but the second is exclusive, as it effectively is '2011/02/27 00:00:00'

Tuesday, June 1, 2021
 
Corsair
answered 7 Months ago
64

You will want to use the YYYYMMDD for unambiguous date determination in SQL Server.

insert into table1(approvaldate)values('20120618 10:34:09 AM');

If you are married to the dd-mm-yy hh:mm:ss xm format, you will need to use CONVERT with the specific style.

insert into table1 (approvaldate)
       values (convert(datetime,'18-06-12 10:34:09 PM',5));

5 here is the style for Italian dates. Well, not just Italians, but that's the culture it's attributed to in Books Online.

Wednesday, June 2, 2021
 
keyBeatz
answered 7 Months ago
55

Assuming you have:

DateTime? date = GetDate();
command.Parameters.Add("@date").Value = date;

in case when date == null you want to insert SQL NULL i.e. DBNull.Value so you should do next:

DateTime? date = GetDate();
command.Parameters.Add("@date").Value = (object)date ?? DBNull.Value;

which means the same as:

if(date != null)
     // use date
else
     // use DBNull.Value

if you want to take care about nullable datetime in your function you should declare it next way:

private object GetDate()
{
    DateTime date;
    return DateTime.TryParse(selectedDate, out date) ? date : DBNull.Value;
}

command.Parameters.Add("@date").Value = GetDate();

but I don't recommend to do that and use next:

command.Parameters.Add("@date").Value = (object)GetDate() ?? DBNull.Value;
Wednesday, August 4, 2021
 
xosp7tom
answered 5 Months ago
20

This all depends on what precision you want to work with. Do you want to match just the date portion or the date and time without milliseconds?

Here's a few ways to change the precision of the data:

CREATE TABLE #dates ( val DATETIME2 );

INSERT INTO #dates ( val ) VALUES ( SYSDATETIME());   
INSERT INTO #dates ( val ) VALUES ( SYSDATETIME());    
INSERT INTO #dates ( val ) VALUES ( SYSDATETIME());

SELECT d.val AsOriginalValue ,
       CAST(d.val AS DATETIME) AsDateTime ,
       CAST(d.val AS DATETIME2(0)) AsDateTime2_0 ,
       CAST(d.val AS DATE) AsDate
FROM   #dates AS d;

DROP TABLE #dates;

Results in:

AsOriginalValue             AsDateTime              AsDateTime2_0             AsDate
--------------------------- ----------------------- ---------------------- ----------
2018-01-25 14:35:14.3660549 2018-01-25 14:35:14.367 2018-01-25 14:35:14    2018-01-25
2018-01-25 14:35:14.3665552 2018-01-25 14:35:14.367 2018-01-25 14:35:14    2018-01-25
2018-01-25 14:35:14.3670555 2018-01-25 14:35:14.367 2018-01-25 14:35:14    2018-01-25

NOTE: beware of rounding.

Tuesday, August 24, 2021
 
andrew.fox
answered 4 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share