Date format YYYYMMDD

[ajp44]

Hi

I am new to SQL coding - need some advice

I have data with dates but the dates I have are currently "YYYYMMDD" which is fine and I don't want to change

But the data I wish to bring back needs to be data from the last 7 days

So

"Error Reporting Date" minus 7 days

Thanks
Andy

[ajp44]

This is my current query

But instead of SSJOBH"."Error reporting date">=20150513 I need it to bring back the past 7 days data

   SELECT "SSJOBH"."Customer site", "DC_Customers_Payers"."Customer name", "DC_Customers_Payers"."Customer address 1", "DC_Customers_Payers"."Customer address 2", "DC_Customers_Payers"."Customer address 3", "DC_Customers_Payers"."Customer address 4", "DC_Customers_Payers"."Postal code", "DC_Customers_Payers"."Payer Customer number", "DC_Customers_Payers"."Payer Customer name",   "SSHEAD"."Customer order number",  right( "SSJOBH"."Error reporting date", 2) + '/' + substring(convert(VARCHAR(8),  "SSJOBH"."Error reporting date"), 5, 2) + '/' + left( "SSJOBH"."Error reporting date", 4) AS "Error Reporting Date",   "SSJOBH"."Error report time",  right( "SSJOBH"."Actual start date - assignment", 2) + '/' + substring(convert(VARCHAR(8),  "SSJOBH"."Actual start date - assignment"), 5, 2) + '/' + left( "SSJOBH"."Actual start date - assignment", 4) AS "Actual Start Date",  "SSJOBH"."Actual start time - assignment",  right( "SSJOBH"."Actual finish date - assignment", 2) + '/' + substring(convert(VARCHAR(8),  "SSJOBH"."Actual finish date - assignment"), 5, 2) + '/' + left( "SSJOBH"."Actual finish date - assignment", 4) AS "Actual finish Date",  "SSJOBH"."Actual finish time - assignment",   "SSHEAD"."Remark",      "SSJOBH"."Assignment type",    "SSJOBH"."Status service assignment",   "DC_Customers_Payers"."Your reference 2",     "SSJOBH"."Error symptom",   "SSJOBH"."Error symptom text (/SEETX1)",   "SSJOBH"."Error symptom text (/SEETX2)",   "SSJOBH"."Error cause", "SSJOBH"."Action text",   "SSJOBH"."Text (/SEMTX1)",   "SSJOBH"."Text (/SEMTX2)",   "SSJOBH"."Text (/SEMTX3)",   "SSJOBH"."Text (/SEMTX4)",   "SSJOBH"."Action",   right("SSJOBH"."Last start date", 2) + '/' + substring(convert(VARCHAR(8),  "SSJOBH"."Last start date"), 5, 2) + '/' + left( "SSJOBH"."Last start date", 4) AS "Last Start Date",  "SSJOBH"."Last start time",  "DC_Customers_Payers"."Facility",   "SSJOBH"."Service manager",   "SSJOBH"."Technician",   "Staff"."Name (/EAEMNM)",   "SSJOBH"."Priority"  FROM   (("BPW_Live_Datamarts"."dbo"."SSHEAD" "SSHEAD" INNER JOIN "BPW_Live_Datamarts"."dbo"."SSJOBH" "SSJOBH" ON ("SSHEAD"."Company"="SSJOBH"."Company") AND ("SSHEAD"."Customer order number"="SSJOBH"."Customer order number")) INNER JOIN "BPW_Live_Datamarts"."dbo"."DC_Customers&Payers" "DC_Customers_Payers" ON (("SSHEAD"."Customer number"="DC_Customers_Payers"."Customer number") AND ("SSHEAD"."Customer group"="DC_Customers_Payers"."Customer group")) AND ("SSHEAD"."Company"="DC_Customers_Payers"."Company")) INNER JOIN "BPW_Live_Datamarts"."dbo"."Staff" "Staff" ON ("SSJOBH"."Company"="Staff"."Company") AND ("SSJOBH"."Technician"="Staff"."Technician")   WHERE  "SSJOBH"."Customer site" LIKE N'Y%' 
AND 
SSJOBH"."Error reporting date">=20150513 
AND 
("SSJOBH"."Service manager"=N'1000' OR "SSJOBH"."Service manager"=N'2000' OR "SSJOBH"."Service manager"=N'3000' OR "SSJOBH"."Service manager"=N'4000') 
AND
NOT ("SSJOBH"."Assignment type" LIKE N'I%' OR "SSJOBH"."Assignment type" LIKE N'S%' OR "SSJOBH"."Assignment type" LIKE N'U%' OR "SSJOBH"."Assignment type" LIKE N'X%')  AND  NOT ("SSJOBH"."Error symptom" LIKE N'6F%' OR "SSJOBH"."Error symptom" LIKE N'6I%' OR "SSJOBH"."Error symptom" LIKE N'IN%' OR "SSJOBH"."Error symptom" LIKE N'6U%' OR "SSJOBH"."Error symptom" LIKE N'6MI%' OR "SSJOBH"."Error symptom" LIKE N'6CL%')   ORDER BY "SSJOBH"."Error reporting date", "SSJOBH"."Error report time"

[Rabbit]

It sounds like you're storing the date as an integer or a string. That's usually a bad idea, it makes querying dates more difficult. It's better if you can store it as a date.

If you can't, you'll need to convert it to a date in your query and use the DateAdd function to subtract 7 days.

[computerfox]

I agree with Rabbit, ideally, you want to store your dates as date types or timestamp types, but sometimes you can't when you have another date-like field. You can use char or text then format it into a date, but definitely not int. Now when you want to convert the field type to a different type, you risk corrupting the value for that field in the records.