SQL and Dates

5 pts.
Tags:
SQL
SQL Query
SQL Server
Can someone date a look at this SQL and recommend a cleaner way to go about this? [BMDTOR is a date field in the file stored as CCYYMMDD] SELECT (DAYS(CURRENT DATE)-DAYS(SUBSTR(BMDTOR,5,2)||'/'||SUBSTR(BMDTOR,7,2)||'/'||SUBSTR(BMDTOR,3,2)))/30 AGE, SUBSTR(BMDTOR,5,2)||'/'||SUBSTR(BMDTOR,7,2)||'/'||SUBSTR(BMDTOR,3,2), (CURRENT DATE) FROM XXXXDATA/FILEXX

Answer Wiki

Thanks. We'll let you know when a new response is added.

Are you trying to find the # of days from today to the date in your table?

If so use the datediff function… datediff(DAY,getdate(),BMDTOR)

Discuss This Question: 5  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    Can someone date a look at this SQL... It'd be a lot easier if we knew what you wanted the SQL to do. When I read the SQL, I don't get a good understanding of what it's doing. What does your DAYS() function do? I'm not aware of a DAYS() function in SQL Server, so I assume you wrote that function or someone else at your site wrote it. BMDTOR is a date field in the file stored as CCYYMMDD Does that mean that BMDTOR is a DATE data type? Or is it some character or numeric data type that holds a 'date' value? (If it's a DATE data type, what do you mean by it being "stored as CCYYMMDD"?) Tom
    125,585 pointsBadges:
    report
  • carlosdl
    This part: "FROM XXXXDATA/FILEXX" Makes me think this is not related to Sql Server.
    68,495 pointsBadges:
    report
  • carlosdl
    And it seems that DAYS is a DB2 function.
    68,495 pointsBadges:
    report
  • TomLiotta
    Carlosdl seems on the right track. It looks like DB2 in the AS/400 line, using system-naming convention. In that case, it looks as if the query is doing a kind of aging in 30-day increments. It also probably means that BMDTOR isn't actually a DATE column; it's a CHAR column that holds a kind of representation of a date. And that implies that the query in the question is about as good as it's going to get. There are things that could be done to make future queries easier. For example, a VIEW named FILEXX_View (or some other name) could be created that does the SUBSTR() operations to generate a real DATE. The BMDTOR column could be transformed into a column named BMDTOR_Date (or some other name). Then the query could be written over the VIEW and perhaps look more like this:
    SELECT (DAYS(CURRENT DATE)-DAYS(BMDTOR_Date))/30 AGE,
    BMDTOR_Date, (CURRENT DATE)
    FROM XXXXDATA/FILEXX_View
    Tom
    125,585 pointsBadges:
    report
  • Oldmickey
    You can use the DB2 function Date(BMDTOR) to convert to date then DAY function will work
    65 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following