Subtract Days From Date Fields in Microsoft SQL

When storing Date data into a table date field in Microsoft SQL the data is stored down to a millisecond.

Example:

DWSTOREDATETIME
-----------------------
2010-01-04 22:18:11.000
2010-01-04 22:18:11.000
2010-01-04 22:18:11.000

Now, if you want to compare that value lets says to another date (just date … not time or millisecond) it will complicate things a little bit.

My solution:

I will convert the data date field in Microsoft SQL to a 8 digit format and the compare or subtract or whatever the requirement may be. Here are some examples:

Conversion dates to US format:

SELECT CONVERT(VARCHAR(10) as FormatedDate,
FROM [dw51b_dwdata].[dbo].[HR_SYNC]

Results:

FormatedDates
-------------
01/04/2010
01/04/2010
01/04/2010
01/04/2010

Subtracting days from dates:

SELECT CONVERT(VARCHAR(10), [DWSTOREDATETIME] - 1, 101) as DateMinusOne
FROM [dw51b_dwdata].[dbo].[HR_SYNC]

Results:

DateMinusOne
------------
01/03/2010
01/03/2010
01/03/2010
01/03/2010

Adding days to dates:

SELECT CONVERT(VARCHAR(10), [DWSTOREDATETIME] - 1, 101) as DateMinusOne
FROM [dw51b_dwdata].[dbo].[HR_SYNC]

Results:

DateMinusOne
------------
01/03/2010
01/03/2010
01/03/2010
01/03/2010

Comparing dates with yesterday’s date:

SELECT CONVERT(VARCHAR(10), [DWSTOREDATETIME] - 1, 101) as DateMinusOne
FROM [dw51b_dwdata].[dbo].[HR_SYNC]
WHERE CONVERT(VARCHAR(10), [DWSTOREDATETIME], 101) <= CONVERT(VARCHAR(10), GETDATE() - 60, 101)

Results:
DateMinusOne
------------
01/03/2010
01/03/2010
01/03/2010
01/03/2010

Note: I am subtracting 60 days in the last example.

About onlinejt

Blogger

No comments yet.

Leave a Reply

*