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.