T-SQL – Converting and Subtracting Two Dates

datet-sql

I'm trying to get the numeric difference between two dates. (Ex: 11/30/2018 – 11/05/2018 = 25)

One of the dates (@EndOfMonth) represent the last day of the prior month and is being converted into a 'yyyymmdd' format

BEGIN
DECLARE @EndOfMonth AS VARCHAR(10)
SET @EndOfMonth = (SELECT CONVERT(VARCHAR, DATEADD(m, DATEDIFF(m, 0, DATEADD(m, 1, DATEADD(MM, -1, @maxDwdate))), -1), 112))

The second date that I'm looking to subtract from @EndOfMonth, is a field named "DUEDATE" and it's in a 'mm/dd/yyyy' format.

Best Answer

This is fairly simple to achieve, the below example code shows how to do this (replace the DueDate variable with your DUEDATE column from your table. Basically, you use DATEDIFF to get the start of the month for DUEDATE, add one month to this date then substract one day. This will always give you the EoM date for the DUEDATE month, regardless fo the number of days in that month.

You can then simply do a DATEDIFF to work out the number of days between the two dates. SQL Server will implicitly convert yyyymmdd into a DATETIME value for the calculation, but if its possible, you should avoid converting the EndOfMonth variable to a string for performance reasons.

DECLARE @EndOfMonth VARCHAR(10),
    @DueDate DATETIME = '12/14/2018'

SELECT @EndOfMonth = CONVERT(VARCHAR(10), DATEADD(d, -1, DATEADD(m, 1, DATEADD(m, DATEDIFF(m, 0, @DueDate), 0))), 112)

SELECT DATEDIFF(d, @DueDate, @EndOfMonth)

Related Solutions

Mysql – How to display all the dates between multiple two dates in a table

The easiest way is to have a calendar table, defined in the following way:

CREATE TABLE calendar
(
    a_day date PRIMARY KEY
) ;

... and filled with all the relevant dates (i.e.: all days from 1990-1-1 to 2100-12-31). For the sake of simplicity, we will fill it only with year 2013:

INSERT INTO 
     calendar (a_day)
VALUES
    ('2013-01-01'),
    ('2013-01-02'),
    ('2013-01-03'),
    ('2013-01-04'),
    ('2013-01-05'),
    -- everything up to
    ('2013-12-31') ;

At this point, you can just have a JOIN with the two tables; with the join condition not being an equality, but a range condition:

SELECT
     t.id, c.a_day
FROM
     t
     JOIN calendar c ON c.a_day BETWEEN t.start_date AND t.end_date 
ORDER BY
     t.id, c.a_day ;

... and get

id | a_day     
-: | :---------
 1 | 2013-01-14
 1 | 2013-01-15
 1 | 2013-01-16
 1 | 2013-01-17
 1 | 2013-01-18
 2 | 2013-02-01
 2 | 2013-02-02
 2 | 2013-02-03
 2 | 2013-02-04

You can see all the setup at dbfiddle here

SQL Server – Handling Mixed Varchar and Float Data Types

The output from the CASE can only be of one data type. The first branch is decimal and the second is nvarchar(255).

According to the Data Type Precedence (Transact-SQL) rules in SQL Server the resulting data type for the CASE is decimal.

Add one more cast to nvarchar(255) in the first branch of the CASE.

SELECT
CASE 
    WHEN [Parcel Number 1] like '%E+%'
    THEN CAST(CAST(CAST([Parcel Number 1] AS FLOAT) AS DECIMAL) AS NVARCHAR(255))
    ELSE [Parcel Number 1]
END
FROM XLSimport

Best Answer

Related Solutions

Mysql – How to display all the dates between multiple two dates in a table

SQL Server – Handling Mixed Varchar and Float Data Types

Related Question