SQl Query

I have a query where I need to return records that are modified today.  I assume I can use the Modified ON column, but not sure how to word the Where clause.  Any help would be appreciated.

Thanks

SELECT     AYR_accountsreceivablesBase.ModifiedOn, AYR_accountsreceivablesExtensionBase.AYR_additionalmsns, AYR_accountsreceivablesExtensionBase.AYR_DueDate, 
                      AYR_accountsreceivablesExtensionBase.AYR_InvoiceType, AYR_accountsreceivablesExtensionBase.AYR_name, 
                      AYR_accountsreceivablesExtensionBase.AYR_Notes, AYR_accountsreceivablesExtensionBase.AYR_OpenClosed, 
                      AYR_accountsreceivablesExtensionBase.AYR_OtherMSN, AYR_accountsreceivablesExtensionBase.AYR_TotalAROS, 
                      AYR_accountsreceivablesExtensionBase.ayr_totalaros_Base, AYR_accountsreceivablesExtensionBase.AYR_TotalSDRcvd, 
                      AYR_accountsreceivablesExtensionBase.ayr_totalsdrcvd_Base, AYR_accountsreceivablesExtensionBase.AYR_MSN, 
                      AYR_accountsreceivablesExtensionBase.AYR_LOIOMName, AYR_accountsreceivablesExtensionBase.AYR_TotalOtherReceived, 
                      AYR_accountsreceivablesExtensionBase.ayr_accountid, AYR_accountsreceivablesExtensionBase.ayr_totalotherreceived_Base
FROM         AYR_accountsreceivablesBase INNER JOIN
                      AYR_accountsreceivablesExtensionBase ON 
                      AYR_accountsreceivablesBase.AYR_accountsreceivablesId = AYR_accountsreceivablesExtensionBase.AYR_accountsreceivablesId

Open in new window

bduengesAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
Let's try that again:
SELECT  r.ModifiedOn,
        re.AYR_additionalmsns,
        re.AYR_DueDate,
        re.AYR_InvoiceType,
        re.AYR_name,
        re.AYR_Notes,
        re.AYR_OpenClosed,
        re.AYR_OtherMSN,
        re.AYR_TotalAROS,
        re.ayr_totalaros_Base,
        re.AYR_TotalSDRcvd,
        re.ayr_totalsdrcvd_Base,
        re.AYR_MSN,
        re.AYR_LOIOMName,
        re.AYR_TotalOtherReceived,
        re.ayr_accountid,
        re.ayr_totalotherreceived_Base
FROM    AYR_accountsreceivablesBase r
        INNER JOIN AYR_accountsreceivablesExtensionBase re ON r.AYR_accountsreceivablesId = re.AYR_accountsreceivablesId
WHERE   r.ModifiedOn >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))

Open in new window

0
 
tigin44Commented:
try this
SELECT     AYR_accountsreceivablesBase.ModifiedOn, AYR_accountsreceivablesExtensionBase.AYR_additionalmsns, AYR_accountsreceivablesExtensionBase.AYR_DueDate, 
                      AYR_accountsreceivablesExtensionBase.AYR_InvoiceType, AYR_accountsreceivablesExtensionBase.AYR_name, 
                      AYR_accountsreceivablesExtensionBase.AYR_Notes, AYR_accountsreceivablesExtensionBase.AYR_OpenClosed, 
                      AYR_accountsreceivablesExtensionBase.AYR_OtherMSN, AYR_accountsreceivablesExtensionBase.AYR_TotalAROS, 
                      AYR_accountsreceivablesExtensionBase.ayr_totalaros_Base, AYR_accountsreceivablesExtensionBase.AYR_TotalSDRcvd, 
                      AYR_accountsreceivablesExtensionBase.ayr_totalsdrcvd_Base, AYR_accountsreceivablesExtensionBase.AYR_MSN, 
                      AYR_accountsreceivablesExtensionBase.AYR_LOIOMName, AYR_accountsreceivablesExtensionBase.AYR_TotalOtherReceived, 
                      AYR_accountsreceivablesExtensionBase.ayr_accountid, AYR_accountsreceivablesExtensionBase.ayr_totalotherreceived_Base
FROM         AYR_accountsreceivablesBase INNER JOIN
                      AYR_accountsreceivablesExtensionBase ON 
                      AYR_accountsreceivablesBase.AYR_accountsreceivablesId = AYR_accountsreceivablesExtensionBase.AYR_accountsreceivablesId
WHERE AYR_accountsreceivablesBase.ModifiedOn > CONVERT(datetime, CONVERT(date, getdate(), 101), 101)

Open in new window

0
 
vastoCommented:
if ModifiedOn  contains just Date:

WHERE AYR_accountsreceivablesBase.ModifiedOn = convert(datetime,floor(convert(decimal(19,9),GETDATE())))

if it contains Date and Time:

WHERE convert(datetime,floor(convert(decimal(19,9),GETDATE()))) <= AYR_accountsreceivablesBase.ModifiedOn
AND AYR_accountsreceivablesBase.ModifiedOn < dateadd(d,1convert(datetime,floor(convert(decimal(19,9),GETDATE()))))
0
 
Anthony PerkinsCommented:
Something like this:
SELECT  r.ModifiedOn,
        r.AYR_additionalmsns,
        r.AYR_DueDate,
        r.AYR_InvoiceType,
        r.AYR_name,
        r.AYR_Notes,
        r.AYR_OpenClosed,
        r.AYR_OtherMSN,
        r.AYR_TotalAROS,
        r.ayr_totalaros_Base,
        r.AYR_TotalSDRcvd,
        r.ayr_totalsdrcvd_Base,
        r.AYR_MSN,
        r.AYR_LOIOMName,
        r.AYR_TotalOtherReceived,
        r.ayr_accountid,
        r.ayr_totalotherreceived_Base
FROM    AYR_accountsreceivablesBase r
        INNER JOIN AYR_accountsreceivablesExtensionBase re ON r.AYR_accountsreceivablesId = r.AYR_accountsreceivablesId
WHERE   r.ModifiedOn >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, GETDATE()))

Open in new window

0
 
bduengesAuthor Commented:

acperkins,

Your code worked, but the query I should be using is this and I tried adding the line you indicated but it gave me more than today's modified records.
SELECT     AYR_accountsreceivablesExtensionBase.AYR_Notes, AYR_accountsreceivablesExtensionBase.AYR_OpenClosed, 
                      AYR_accountsreceivablesExtensionBase.AYR_InvoiceType, 
                      CASE AYR_accountsreceivablesExtensionBase.AYR_InvoiceType WHEN 1 THEN 'Rental' WHEN 2 THEN 'Maintenance' WHEN 3 THEN 'Rental/Maintenance' WHEN 4 THEN
                       'LOI' WHEN 5 THEN 'Other' WHEN 6 THEN 'Loan' WHEN 7 THEN 'Taxes' WHEN 8 THEN 'Fees' ELSE 'Unknown' END AS INVTYPE, 
                      AYR_accountsreceivablesExtensionBase.ayr_totalaros_Base, AYR_accountsreceivablesExtensionBase.AYR_TotalAROS, 
                      AYR_accountsreceivablesExtensionBase.AYR_DueDate, AYR_accountsreceivablesExtensionBase.ayr_accountid, 
                      AYR_accountsreceivablesExtensionBase.AYR_MSN, AccountBase.Name, AYR_accountsreceivablesBase.ModifiedOn
FROM         AYR_accountsreceivablesExtensionBase INNER JOIN
                      AccountBase ON AYR_accountsreceivablesExtensionBase.ayr_accountid = AccountBase.AccountId INNER JOIN
                      AYR_accountsreceivablesBase ON 
                      AYR_accountsreceivablesExtensionBase.AYR_accountsreceivablesId = AYR_accountsreceivablesBase.AYR_accountsreceivablesId
WHERE     (AYR_accountsreceivablesExtensionBase.AYR_InvoiceType = 1) AND (AYR_accountsreceivablesExtensionBase.AYR_OpenClosed = 1) OR
                      (AYR_accountsreceivablesExtensionBase.AYR_InvoiceType = 2) AND (AYR_accountsreceivablesExtensionBase.AYR_OpenClosed = 1) OR
                      (AYR_accountsreceivablesExtensionBase.AYR_InvoiceType = 3) AND (AYR_accountsreceivablesExtensionBase.AYR_OpenClosed = 1) OR
                      (AYR_accountsreceivablesExtensionBase.AYR_InvoiceType = 6) AND (AYR_accountsreceivablesExtensionBase.AYR_OpenClosed = 1)
ORDER BY AYR_accountsreceivablesExtensionBase.AYR_DueDate

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.