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
3 1764
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"
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.
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Simon Brooke |
last post by:
I'm investigating a bug a customer has reported in our database
abstraction layer, and it's making me very unhappy.
Brief summary:
I have a database abstraction layer which is intended to...
|
by: Dario Di Bella |
last post by:
Hi all,
we have the following urgent issue affecting our development team.
Initially we had one particular workstation that failed executing
queries on a DB2 database, raising an invalid date...
|
by: FlexCrush |
last post by:
Is it possible to convert a date format ("yyyymmdd") to ("mm/dd/yy")?
Thank you in advance
|
by: pcouas |
last post by:
Hi,
I need to create an XSD file for XML document
In mys XML document i could have various date format
YYYYMMDD, YYMMDD, DDMMYY, DDMMYYYY
I know theses formats before creating xsd file, but i...
|
by: DontellTrevell via AccessMonster.com |
last post by:
HELP!!....I need to calculate the numer of days elapsed between two field.
But, the date format is YYYYMMDD. How can i accomplsh this?
--
Dontell Trevell
Message posted via AccessMonster.com...
|
by: YSpa |
last post by:
Hi,
I'm using SQL-Server Express 2005 on Windows XP Prof. and after working properly for some time my asp.net application suddenly gave the error that my DateFormat wasn't accepted while using...
|
by: naaniibabu |
last post by:
I have one number i want to convert in to a date format
the size of the value is =8 Char
20091212
i ant to conver it in to a date format
my sysyem doent understand wether its adate or not its...
|
by: conerlysduck |
last post by:
Hello,
I am trying to change MMDDYYYY to European format YYYYMMDD. I have this in my query:
"20" & Right(!,2) & Mid(!,3,2) & Left(!,2)
which gives me YYYYDDMM. Does anyone know to get the...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |