Tag: datetime

TSQL Convert DateTime & Timezone Offset (in Minutes) to another Time Zone

In my SQL Server 2008 R2 database, I have a LastHWScan column (datetime) and a TimzoneOffset column (int). The LastHWScan column stores the datetime that is local to the workstation being scanned. The TimezoneOffset column stores the timezone offset as either a positive or negative number of minutes. Dallas is -300, Bucharest is 180. How […]

Converting Partial String dates to datetime in SQL

I have a free text column containing dates provided by my client. The column will have values something like the below. 2014-02 21-12-2013 2014-06-28 9/10/2014 2009 It may contain partial dates, different date format. I need to convert these values to datetime. I tried many ways to convert the data and some are below. I […]

Update query to find and change portion of MS SQL DateTime

I have various datetime stamps on a expiration field (datetime). The datetime in that field for each record varies. For example, 2015-12-31 04:59:00:000, 2014-12-31 17:00:00:000, 2020-12-15 04:00:00:000 Trying to write a query to find all datetime that have an ENDING that is not equal != to 05:00:00:000. Then an update query to update the end […]

Building a timestamp from date, time, and meridian fields

I have a legacy system that uses four discrete fields to capture a datetime: one for the date (ex. 10/23/2015), one for the hour (ex, 10), one for the minutes (ex, 45), and one for the meridian (A or P). I am trying to build a scalar function in MSSQL 2008 R2 that will take […]

Inserting date from Access DB into SQL Server 2008R2

It seemed to be an easy task but I fail and do not find a solution for my problem: I have a local table in Access 2010 with a date/time column and I want to update a column in a SQL Server table with a datatype date. Sending the date/time values direct to the SQL […]

SQL Server 2008 R2 import from access fails on datetime column

I’m trying to import an Access 2010 DB table (*.accdb) to SQL Server 2008 R2, as a new table not to an existing one. I’m doing it through SQL Server’s “Import an Export Data Wizard”. I have two datetime columns that I know for a fact that contain some non valid datetime values (don’t ask […]

Datetime field in XML gets an hour added for some reason when storing in database via SSIS

I have an XML that I’m pulling in, which has datetime fields: <startTime>2015-11-01T00:00:00-05:00</startTime> <endTime>2015-12-01T00:00:00-05:00</endTime> The relevant portion of the XSD is as follows: <xs:schema attributeFormDefault=”unqualified” elementFormDefault=”qualified” targetNamespace=”http://www.foo.com/bar” xmlns:xs=”http://www.w3.org/2001/XMLSchema”> …. <xs:element minOccurs=”0″ name=”startTime” type=”xs:dateTime” /> <xs:element minOccurs=”0″ name=”endTime” type=”xs:dateTime” /> When I process the file using SSIS, I’m using an XML Source component that fetches data […]

GMT DateTime Conversion between different TimeZones

Web application is hosted on a server with (UTC+10.00)Canberra,Sydney,Melbourne TimeZone. When a user from Melbourne creates an event it saves the datetime to database in GMT format. if the user selection is 23/12/2015 3:30:00 AM value saved to the DB will be 2015-12-22 16:30:00.000 Now when a user from (UTC+10.00)Brisbane visits the application it’s still […]

creating a report of how many times something occured

I need to build a query from dates like: 2015-01-01 01:05:15 2015-01-01 01:07:17 2015-01-01 07:09:18 2015-01-01 07:12:41 2015-01-02 04:06:15 2015-01-02 04:15:15 I need to have table in which there will be column for every hour and a row for every day of the month in which there will be the count of how many times […]

SQL Server: selecting a year of account based on a specific date and a date range

I need to apportion some values to a financial year that begins on the 1st December and ends on the 30th November each year. The rows that contain the value fields are in a table (TABLE A) that has a reference number and an incident date Table A ReferenceNumber, Value, IncidentDate 1, 10.00, 01/12/14 2, […]

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