I have two columns Allocated and Date Delivery
The output comes as
02/19/2021 for Allocated
2021-02-01 10:19:57 -0700 for date delivery
I need to run report to get output for last 7 days
Please provide sample table and data - with expected results. Also - what version of SQL Server are you working with?
You say you need output for last 7 days - last 7 days from when (current?) and compared to which column - Allocated or [Date Delivery]?
What are the column definitions - if [Date Delivery] is a DATETIMEOFFSET (which it should be - based on the included offset), and you are looking for the last 7 days from that date - then you need to identify whether you want that calculated from UTC or local datetime.
As jeff asked, what are the data types (date?; datetimeoffset?; etc.) for those columns? That's critical to writing the code correctly.
Using Microsoft server
The output should be from today’s date ie 29 April 2021 for the past 7 days ie 23 April
The column from where I need output is Allocated
This is the syntax
Select order,Facility,Ordernumber, user 20 as Allocated, user14 as datedelivery from Orders
I need output from user20 from today date for the past 7 days
I need from EST
Sample data using create/insert so we can test? Expected output from given sample data? Data types of each column?
Since you want the data from Allocated - which appears to be named User20 and is almost certainly a string type of column, you are almost certainly going to run into issues. But given that...
WHERE User20 >= dateadd(day, -7, cast(getdate() as date))
Got an error says conversion failed when converting date and /or time from character String
Wondering can we get data from User14 ?
Just in case if user20 doesn’t work however this is the last option
I’ve replied to the post kindly let me know just in case if you need anything from my end
you were asked to provide sample data, which you have not yet
declare @sampledata table(col1 int, col2 varchar(10) ) --as example
insert into @sampledata
select 1, 'who dat' union
select 1, 'shishkabob'
until you do that folks will not be able to help you, it would be guess work.
2 Likes
Sorry was caught up with some IMp work here you go I have column D and column E where I have issues
provide the data in the following format
declare @sampledata table(col1 int, col2 varchar(10) ) --as example
insert into @sampledata
select 1, 'who dat' union
select 1, 'shishkabob'
if you do not, no one will be able to help you. photos are nice, but this is the best way you can provide in order for us to give you a proper answer. help us help you
nope. 
no photos.
declare @sampledata table(col1 int, col2 varchar(10) ) --as example
insert into @sampledata
select 1, 'who dat' union
select 1, 'shishkabob'
post here a similar script as above ^^ for your table.
I expected this - as the column is likely defined as a string (varchar). This presents an even bigger challenge - because you cannot be sure the data in that column is always stored in MM/DD/YYYY format.
It could be stored in DD/MM/YYYY format - or YYYY-MM-DD or YYYYMMDD or YYYY-DD-MM or YYYY-MM or YYYYMM or MMMYY (e.g. JAN15) or any number of other variations.
You can try this:
WHERE try_cast(User20 As date) >= dateadd(day, -7, cast(getdate() as date))
But - this will exclude all rows where User20 cannot be cast as a date. You really need to identify those rows and determine how you want to process them...or just accept that the results of your query will be incorrect.
As for using the other column - not a chance - there is no way to tell what that string value really means. Is it the UTC time or local time with offset - that is, was the value converted to local time with the offset applied before it was saved - or was the value UTC and showing the offset that should be applied?
Taking your example: 2021-02-01 10:19:57 -0700
Do we subtract 7 hours from that date/time to get to the correct value - or was the offset already applied when the data was filed? Absolutely no way to know unless that column's data type is a DATETIMEOFFSET (which by name it surely is not).