Tag: datetime

Adding date difference of two dates in Sql for all columns

I have a table with two date columns as ArrivalDate and DepartureDate. I need to calculate the total time period in hours and minutes (not date) of all the entries in the table . I can get the time period of a particular record via datediff but what i need is the sum of all […]

Equivalent of Objective-C's setTimeZone in SQL query

I need to do some date-to-string conversions in a SQL query (SQL Server 2012). I’m familiar with CONVERT(DATETIME,myString,127) function, but this will give me a string with a timezone appended at the end. I don’t want to show any timezone information in the string, but I want the time that gets shown expressed in UTC. […]

jpa mssql create datetime column with TemporalType.TIMESTAMP and portability

I have an entity with a following column definition: @Temporal(TemporalType.TIMESTAMP) private Date dateStart; @Temporal(TemporalType.TIMESTAMP) private Date dateEnd; In derby jpa automatically create TIMESTAMP columns. When I want to use the same application with Microsoft SQL Server, I have problem, because jpa automatically create column with TIMESTAMP type. In MSSQL only one TIMESTAMP column per table […]

Standardizing a column in an SQL Table

I have a column in my SQL table named ‘Dates’, in which the dates are stored in different formats, like: 25/5/2000, 1st June 2013, 5/1/1996, Jan 23,1990 etc. I want to convert all these dates to a single format e.g. DD/MM/YYYY or MM/DD/YYYY. How can I do so?

sql select command , from date to date

I have a grid with some data and 2 cells of its date range (grid start date –> grid end date) A user got an option to enter a date range (also user start date and user end date) and my goal is to show the grid to the user with the rows that any […]

Filtering and displaying data in gridview from database based on date constraint

Here is my tables: create table users ( userid int identity(1,1) primary key, username varchar(150), password varchar(20), email varchar(150), mobile varchar(50), address varchar(2000), service_start_date datetime, createdate datetime, modifydate datetime, deletedate datetime, tablestatus varchar(50), roles varchar(50) ) create table service_transaction ( srvid int identity(1,1), srv_startdate datetime, srv_enddate datetime, script varchar(1000), position_type varchar(100), entryprice varchar(100), targetprice varchar(100), […]

Error converting a value of datetime to mm/dd/yyyy format

I am trying to retrieve a datetime value by converting it into just Date format like mm/dd/yyyy using the following query snippet CASE WHEN co.RequestID is not NULL THEN CONVERT(VARCHAR(10), DateFrom, 101) WHEN nis.[867_key] is not NULL THEN CONVERT(VARCHAR(10), nis.ServicePeriodStart, 101) ELSE CONVERT(VARCHAR(10), q2.ServicePeriodStart, 101) END as READ_START But for some reason it gave me […]

How to convert nvarchar datetime into datetime in SQL sever 2008?

I have a table where activedate is the nvarchar column which I need to convert into datetime to compare with another datetime field. My current data is in the following format: 3/19/2013 4:18:59 I have tried this: alter table [stresstest].[dbo].[temporaryVSDB] alter column activateDate datetime; and convert(datetime,activateDate,101) But not working. I am getting the following error […]

SQL Server fails on converting string to datetime with valid string

I’m migrating some old, horribly-formatted data to a new system, and am extracting expiry dates from a mass of data in a string column. It has the format <Something>Data</> (not my doing, I’ll add!) One of the elements I need to extract is an expiry date/time. I do it as follows: SELECT convert(datetime, SUBSTRING(EventData, CHARINDEX(‘<ExpiryDate>’, […]

Why did the same code about datetime display differently in text and SQL Server table?

My method has two steps, in order to trace log, I record the time when each step is finished. The code looks like this: //both time are the same private void TestDateTime() { AddNum(1,3); var step1FinishedTime = DateTime.Now; RecordToSqlTable(step1FinishedTime); AddNum(2,3) var step2FinishedTime = DateTime.Now; RecordToSqlTable(step2FinishedTime); } I find that step1FinishedTime and step2FinishedTime are always the […]

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