SQLTeam.com | Weblogs | Forums

Need to convert date from two columns and get output for last 7 days

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

Hope this helps ?

nope. :laughing: 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).