Tag: datetime

Convert date time in SQL

I would like to find out which user are late clock in example clock in time on 9:00Am more than 9:00am declare as late but my result show me more than 10:00Am just count as late DECLARE @clockin as varchar DECLARE @clockout as varchar DECLARE @reportdate as datetime –SET @clockin = CONVERT(108,’08:30′) SET @clockin = […]

Explicit conversion from data type date to int is not allowed

I am trying to calculate AGE of contracts, however I am getting the error Explicit conversion from data type date to int is not allowed This is my code: SELECT Contract_hist.*, IB.*, CAST((CASE WHEN (CAST(CONVERT(INT, Setup.[Start]) – CONVERT(INT,[IB].[Install_Date])) AS INT))/365 <= 0 THEN 0 WHEN (CAST(CONVERT(INT, Setup.[Start]) – CONVERT(INT,[IB].[Install_Date])) AS INT)) / 365 > 0 […]

SSIS expression previous date without DateAdd()

Currently developing a package that passes an expression from a previous date to a filename. The current code I have is the following as a string variable: (DT_WSTR,20)DATEPART(“YYYY”,Dateadd(“DD”,-1,dateadd(“MM”,datediff(“MM”, (DT_DATE) “1900-01-01”,getdate())-2, (DT_DATE) “1900-01-01”))) + RIGHT(“0″+(DT_WSTR,20)DATEPART(“MM”,Dateadd(“DD”,-1,dateadd(“MM”,datediff(“MM”, (DT_DATE) “1900-01-01”,getdate())-5, (DT_DATE) “1900-01-01”))),2) + “01” This currently produces the output of: 20171101 This is currently incorrect because I’d like the […]

How to change default string representation of GETDATE() to include milliseconds?

I’m creating an tool called dbfiddle, and someone has asked how to get more precision from GETDATE() and SYSDATETIME(), which currently show seconds only: SELECT SYSDATETIME(), GETDATE(); GO (No column name) | (No column name) :—————— | :—————— 25/03/2017 14:22:16 | 25/03/2017 14:22:16 dbfiddle here I’m using a JSON API to the database backends, and […]

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 […]

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