field < currentdate - 7 format date

Question

My datetime field is in date format M/dd/yyyy. SQL Server date is in date format yyyy-mm-dd. I need to get the current date -7 days and format the output

Example.

SELECT * FROM [Table]
WHERE Time Generated >= current date - 7 days

Output of current date - 7 should be M/dd/yyyy

SELECT FORMAT(Getdate(), 'MM dd, yyyy')

DECLARE @date varchar(30) = FORMAT(getdate(), 'M/dd/yyyy');  
SELECT Computer, AppFileDisplayName FROM DHAppReliability
WHERE HasCrashesDaily = 'TRUE'
AND TimeGenerated <= DATEADD(DAY, -7, @date)
ORDER BY Computer

Msg 241, Level 16, State 1, Line 2 Conversion failed when converting date and/or time from character string.

Dates have no format, they are binary values. Formats apply only when parsing strings into dates, or formatting dates into strings for display. SSMS will always use the ISO8601 format because it's the only unambiguous format. That doesn't mean the dates have some specific format — Panagiotis Kanavos
what data type is field 'Generated'? when you say your date format is M/dd/yyyy, do you mean it's saved in a string in that format, or that SSMS shows it like that? — Cato

Gordon Linoff Gordon Linoff · Accepted Answer · 2019-07-11T15:34:25

Fix your data! Don't store dates as strings.

In any case, you need to do the comparison using the built-in date/time functionality. That would be:

SELECT Computer, AppFileDisplayName FROM DHAppReliability
WHERE HasCrashesDaily = 'TRUE' AND
      CONVERT(date, TimeGenerated, 101) <= DATEADD(DAY, -7, @date)
ORDER BY Computer

I'm not 100% sure that format 101 is the right format, because you have not provided sample data.

field < currentdate - 7 format date

1 Answers