Trying to create Condition in Universe for Most Recent Date

building semantic layer
1

I have the following code which does parse but returns no data. Looking for any advice.

Thanks.

Schema_Name.dbo.table_name.RECEIVE_DT = GetDate() :?:

spaket (BOB member since 2008-02-28)

2

getdate() returns a date+time. If your comparision column is date only, or if it is date/time but uses midnight (00:00:00) for all values, then it won’t match up. In Oracle you can fix this by applying the trunc() function to sysdate (sysdate is the match to getdate() ) but there does not appear to be a matching function in SQL Server.

I found this article that suggests how to address that.

Dave Rathbun :us: (BOB member since 2002-06-06)

3

Okay. We are on SQL SErver and I verified in SQL Server that the Receive_Dt is (Datetime) data type. Any other suggestions?

spaket (BOB member since 2008-02-28)

4

Also it does not use 00:00;00 for all values. It is a valid date/time stamp as to when we receive the file.

spaket (BOB member since 2008-02-28)

5

Then the only time you will ever get data is if something is received at the exact same second you run the report. :slight_smile:

Dave Rathbun :us: (BOB member since 2002-06-06)

6

I really want to say GetDate() -1 . We receive the files every night and need to get the most current file. Any suggestions on how I can do that given our sestup?

spaket (BOB member since 2008-02-28)

7

Define “most recent” please.

If you mean literally the most recent document that has come in, you need to do a query something like:

doc_id in  (select doc_id from table having receive_date = max(receive_date))

If you want to see everything that has come in over the last 24 hours, simply change it to this:

receive_date >= getdate() - 1

However, I don’t think you can simply subtract one, I think you need to use the dateadd() function to subtract a day. You also need to consider if you want the last 24 hours or yesterday, as the two are not the same.

It’s all about getting the right requirement before writing code. :slight_smile:

Dave Rathbun :us: (BOB member since 2002-06-06)

8

We got this to work. thanks for your help.

CONVERT(VARCHAR(8), schema_name.dbo.M_TRACK_FILE.RECEIVE_DT,112)  = CONVERT(VARCHAR(8), GetDate()-1, 112)

spaket (BOB member since 2008-02-28)