Tag: datetime

Sql server and Excel float to date offset

I’m working with date and float conversion on sql server and excel. This is my query: select getdate(),convert(float, getdate()) I get: 2014-11-21 16:38:49.973 41962,6936339506 If I copy this float number to Excel and I change cell type as date I get this value: 19-nov-2014 What’s that? Why there is an offest of two days?

How to calculate difference between two datetime fields (yyyy-mm-dd hh:mm:ss) in time format (hh:mm:ss) in sql server?

I have 2 columns in my table in sql server – [Occurrence Time (NT)] and [Clearance Time(NT)] having the format yyyy-mm-dd hh:mm:ss and a third column [Outage Duration] with the format hh:mm:ss [Identifier] | [Occurrence Time(NT)] | [Clearance Time(NT)] | [Outage Duration] 4 | 2014-12-28 15:06:33.000 | 2014-12-28 15:18:18.000 | 00:11:45.000 Outage duration is calculated […]

using date between in sql where clause

I have a table A with origdt as a column in it. I want to pull all the records from the table based on the following conditions if today is tuesday then Origtdt between saturdaymorning to yesterday evening else Origtdt between yesterday morning to yesterday evening end Here is the query i wrote and it […]

ADO.NET DateTime sql parameter lose accuracy

I am trying to delete records with ADO.NET for SQL Server, of which there is a sql command with the DateTime type parameter. using (var sqlConnection = new SqlConnection(…)) { var command = sqlConnection.CreateCommand(); command.CommandText = string.Format(“DELETE FROM [{0}] WHERE UtcTimeStamp=@UtcTimeStamp”, …); command.Parameters.Add(new SqlParameter(“@UtcTimeStamp”, DbType.DateTime2) { Value = exactTime }); sqlConnection.Open(); command.ExecuteNonQuery(); } When I […]

Only filter my time in SQL Server where clause

I am new to SQL and Programming in general. I have a datetime field which I want to use to filter my results but only on the time portion. For example I need to run a report from 2pm yesterday to 7am current day. I cannot hard code the dates because this report needs to […]

How to convert date so it joins with date dimension table

I have a ProdDate column in Product table that is in the following format: 1/2/2015 1:22:19 PM I need to join this date column to retrieve more information from DimDate table. I am joining with a column name Date that has the following date format: 1900-01-01 00:00:00.000 What I have tried but still doesn’t work: […]

cast in sql server yyyy/mm/dd instead of dd/mm/yyyy

I have a script to insert 1000 rows of data in a table, in the datetime column I have this information. CAST(N’2015-05-14 00:00:00.000′ AS DateTime) The problem is cast is trying to cast in dd/mm/yyyy format where the input is in yyyy/mm/dd format.

How to change date-time format?

I have a RegDate column of nvarchar(max) type in my table in which dates are stored in mm/dd/yyyy (5/22/2015 11:09:39 PM) and dd-mm-yyyy (19-05-2015 22:55:05) format. I want to get all these entries in one format i.e. dd/mm/yyyy. I tried to convert it by using Convert(varchar(10),cast(vr.RegDate as DATETIME),105) as RegistrationDate but it gives following error: […]

c# conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value

I am putting together a C# Asp.net MVC site. One of the models is SystemUser as shown here public class SystemUser : DefaultModel { public int Id { get; set; } [Required] [StringLength(50, ErrorMessage = “First name cannot be longer than 50 characters.”)] [Column(“FirstName”)] [Display(Name = “First Name”)] public string FirstName { get; set; } […]

T-SQL Looking for the most concise way to summarize a starting and ending date range

Really long story short, I have two datetime fields in a table that I am loading into variables @DateFrom and @DateTo, and I need to “explain” what sort of range they cover in text for presentation on the screen. Although the fields are datetime, the time component will be ignored. I am also declaring @Current […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.