Stack Overflow
Stack Overflow

Questions tagged [datetime2]

A SQL Server type that defines a date that is combined with a time of day that is based on 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.

A SQL Server data type that define a date that is combined with a time of day that is based on a 24-hour clock. datetime2 can be considered as an extension of the existing datetime type that has a larger date range, a larger default fractional precision, and optional user-specified precision.

References and resources

155 questions
1
vote
0 answers

How to format C# DateTimeOffset exactly same as in SQL Server's DateTime2(7) format?

Saving following two DateTime values in SQL Server's DateTime2(7) column results in same DateTime2 value. var dt1 = new DateTimeOffset(638124107765100000, TimeSpan.Zero); var dt2 = new DateTimeOffset(638124107765000000, TimeSpan.Zero); Value in Sql…
Akash Kava
  • 39,066
  • 20
  • 121
  • 167
1
vote
1 answer

Convert timestamp from string to EST time in Synapse

I am new to Synapse Data Warehouse. Currently, I have a timestamp column named last_update_utc as string data type and it is UTC time. I need to add a new column last_update_est with timestamp as datetime2 data type convert from last_update_utc…
Ken Masters
  • 239
  • 2
  • 17
1
vote
1 answer

Converting datetime2 to int

I want to convert my datetime2 column to int for each record. How do I do it without creating a variable? Examples of my datetime2 field: 2020-03-03 10:17:26.0000000 2020-03-03 10:37:35.0000000 etc...
user12571054
1
vote
1 answer

Why can datetime allow this decimal format but datetime2 won't?

I just ran into an issue where my program was not able to convert a string to a datetime2 value. The value was 12/17.2020. When converting this to datetime it worked, but when converting it to datetime2 it didn't. What is the reason for this? How…
Programmer
  • 459
  • 5
  • 20
1
vote
4 answers

Why converting empty value to datetime2 is throwing conversion error in SQL?

In C# I get this value for a datetime2 type: {01/01/0001 00:00:00} Which is because the client is send empty value. In database it throws error: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range…
scaryghost
  • 77
  • 8
1
vote
1 answer

SQL Server : order by time portion of Datetime2 timestamp

I have a table of orders with a datetime2(7) column O_CreatedOn and I would like to order this recordset by the time portion only of the O_CreatedOn column. The reason for this is that I need to generate a report that shows the most prevalent times…
nbardach
  • 123
  • 1
  • 10
1
vote
1 answer

How to convert datetime2 column to bigint with the datediff in place

I have a column named 'order_confirmation_date' that is in Datetime2 format and I need it to work with bigint with the below query that uses datediff b/w the column value and getdate(). SELECT datediff(day, convert(VARCHAR(10), NULLIF(( …
RKKK
  • 37
  • 7
1
vote
1 answer

How does one handle Date/Time component Overflow for Conversion to DATETIME2?

Supposing you have the following atrocious data structure in your SQL Server database to hold Date Times. Year (int) Month (int) Day (int) Hour (int) Minute (int) Second and Millisecond (float) And you need to keep it but create a computed…
Matt Arnold
  • 668
  • 2
  • 8
  • 21
1
vote
1 answer

SQL: Combine DATEDIFF from two columns and update the table with this time

I have recently started with SQL so I'm still learning, this is also my first question so sorry in advance. I have a Table, Messages_History with three columns tmStartTime, tmEndTime and tmTotal, all data typeDATETIME2. tmStartTime …
K.Luth
  • 135
  • 13
1
vote
0 answers

Java Date to SQL Server datetime2 via Hibernate: getting ms rounded like datetime?

My Java fields are defined as Dates, and like some others I have run into the issue of SQL Server's datetime rounding milliseconds strangely (to the nearest 1/300 sec). Accordingly, I changed my tables to datetime2(3) for proper ms precision. Why…
Kevin C
  • 11
  • 2
1
vote
0 answers

Default Value for datetime2 field in EF6 does not works

I am using EF6 code first approach, wherein i have class with CreatedDate field, i have decorated it with [DefaultValue("getutcdate()")] and the Default binding has also been generated by EF, however, when i do not set any value for this field it…
Abbas
  • 4,948
  • 31
  • 95
  • 161
1
vote
1 answer

Passing in variables to SQL Server stored procedure, where clause

I am writing an application responsible for archiving data and we have the configuration in a database table Id | TableName | ColumnName | RetentionAmountInDays 1 | DeviceData | MetricTime | 3 So when faced with…
andrewb
  • 2,995
  • 7
  • 54
  • 95
1
vote
4 answers

Get hour:minutes from DATETIME2

I have a problem in SQL Server 2008 R2. I want to get HH:mm from a DATETIME2 column. When I write this statement: SELECT CONVERT(VARCHAR(5), getdate(), 8) I am getting perfect result that is 11:19, but when I try to get time from: SELECT…
A.Goutam
  • 3,422
  • 9
  • 42
  • 90
1
vote
0 answers

ASP.Net Issue with datetime2 in Code First Entity Framework

I have Game class which has two properties GameStartDate and GameEndDate with DateTime type format. public class Game { [Key] public int GameID { get; set; } [Required, DisplayName("Start Date"),…
Behseini
  • 6,066
  • 23
  • 78
  • 125
1
vote
1 answer

HashBytes with datetime2 field

I am creating a hash key using hashbytes on multiple columns to get performance gain which we are using right now in where clause. alter table dbo.Table1 add HashKey AS CAST(hashbytes('MD5', PID+PNumber+CONVERT([varchar] (50),[DateStamp])…
Chandra Mohan
  • 729
  • 2
  • 10
  • 29
1 2 3
…
10 11