Tag: datetime

SQL Server getdate() switching to/from DST more than a minute later than expected

Running SQL Server 2008 R2 on Windows Server 2008 R2. We’ve recently discovered that the SQL Server getdate() function does not appear to switch to/from daylight saving time right at 2:00am local time as I would expect it to. The server’s timezone is set to “(UTC-06:00) Central Time (US & Canada)” with the “Automatically adjust […]

How to create SQL custom 4-4-5 Finanical Period date table

I’m trying to create a SQL script to generate custom financial date table for reporting between years 2010 – 2030. The calendar is loosely based on a 4-4-5 calendar except there are specific rules for periods. The rules are as follows; The fiscal year always starts on the 1 of January The fiscal year always […]

How do I get the date from a datetime when creating a SQL view?

I have searched for hours and found a lot of information about how do convert a datetime field to a date. All of it works well in my SQL window. However, the minute I try to use in in a view it crashes. Version: SQL Server v17.0 Example: field in the table is: InvoiceDate(datetime,null) data […]

Can PDO return dates as DateTime objects?

Microsoft’s SQL Server driver for PHP can return dates as native PHP DateTime objects, but when accessed via PDO, dates are returned as strings. Is there any way to configure PDO to return dates as objects as well? <?php $server = ‘localhost\\SQLEXPRESS’; $database = ‘MyDatabase’; $username = ‘MyUsername’; $password = ‘MyPassword’; $sql = ‘SELECT CAST(\’2017-01-31\’ […]

Convert Oracle Datetime format query to MS SQL Server Format

I have a Oracle query SELECT to_timestamp(’29-03-17 03:58:34.312000000 PM’,’DD-MM-RR HH12:MI:SS.FF AM’) FROM DUAL I want to convert to SQL Server where I need to retain the Oracle date string i.e ’29-03-17 03:58:34.312000000 PM’: SELECT CONVERT(DATETIME, REPLACE(REPLACE(’29-03-2017 03:58:34.312000000 PM’,’-‘, ‘/’),’000000 ‘, ”), 131) I tried the above query, as 131 format closely matches ’29-03-17 03:58:34.312000000 PM’ […]

Finding max date difference on a single column

in the below table example – Table A, we have entries for four different ID’s 1,2,3,4 with the respective status and its time. I wanted to find the “ID” which took the maximum amount of time to change the “Status” from Started to Completed. In the below example it is ID = 4. I wanted […]

Date parameters returning values outside the range of dates

Using SQL Server Management Studio, I have a query that I’m using date parameters but when I execute the query, I see rows that aren’t in the date range selected. DECLARE @StartDate Date = ‘7/10/2017’ DECLARE @EndDate Date = ‘7/17/2017′ SELECT DISTINCT PROJECTID, ACTIVITYID, ACTIVITYNAME, ISPRIMARYRESOURCE, RESOURCEID, STARTDATE, FINISHDATE, FORMAT(STARTDATE,’dddd’) + ‘, ‘ + FORMAT(STARTDATE,’m’) […]

Saving datetime to MSSQL database from PHP – throws error

I have PHP web app, which has been deployed to several computers without problem. Just in this one case, I have problem when saving datetime to MSSQL database. The message says: SQLSTATE[22007]: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server] Conversion of a varchar data type to a datetime data type resulted in an out-of-range value. […]

How do I map java.util.Date to sys.datetime

I am upgrading our JDBC driver to mssql-jdbc-6.2.1 so we can move to SQL server 2016. Everything works fine with the new driver except our reports. We have 6000+ reports that use java.util.Date. With the previous driver these were treated as DATETIME in SQL but the new driver treats them as DATE. The reports are […]

Setting default parameters for a SQL Server stored procedure

I am trying to set the default value of @Day to the current day. However, I am getting a couple errors on the initial DATEADD(Day, DATEDIFF(Day, 0, GETDATE()), 0) and on all the @Days below it. I thought that the @Day DateTime under the alter procedure declared @Day but I am getting the error: Must […]

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