0

I have date values that are stored in the following format: Thursday, December 19, 2019 12:00 AM

I need to retrieve just the month and day however I have been unsuccesful as I keep getting the time as well. Is there query that will just get the date 12/19 or December 19 as a date because I need to compare it against today's date without the year.

Thanks

Improve this question

1 Answer 1

Reset to default
1

Assuming that you're doing this on something other than Marketing Cloud...

What you have here isn't a date, it's a datetime. While there are SOQL Date functions that can help, their primary use is in filters (i.e. the WHERE clause) and you can't compare the result of a Date function with a Date literal like TODAY or THIS_YEAR. In general, you can't change the type of a field that you get back in the result of a query.

In Apex, the Datetime class does have methods that can extract the Date portion (e.g. date(), see the Datetime documentation for more information) as well as methods to extract the month and day.

While you can use Apex to further refine the results of a query, what you're looking to sounds like it's simple enough that you could use the 'ol formula field workaround. That is, you can't create the filter directly in a query, but you can create a formula field that does. You'd then use that formula field in your query.

Something like

AND(
    MONTH(My_Datetime_Field__c) = MONTH(TODAY()),
    DAY(My_Datetime_Field__c) = DAY(TODAY())
)

Your formula should return a boolean value. Assuming it's named Month_And_Day_Matches__c, your query would end up looking like

[SELECT Id FROM MyObject__c WHERE Month_And_Day_Matches__c = true]
Improve this answer
3
  • But the formula approach is a Bad Idea, as it causes a Full Table Scan, so use with cation.
    – sfdcfox ♦
    Commented Mar 28, 2022 at 0:59
  • I like the formula idea, however I am doing this in Marketing Cloud automation. I do have the option of getting the datetime from the source DE and breaking it up and putting it into a temporary DE. Are there more options if I do something like that? Thanks.
    – gh0st
    Commented Mar 28, 2022 at 1:22
  • @gh0st Then you should edit your question to remove the apex and soql tags, and add the marketing-cloud tag. Marketing cloud is an entirely different beast, and I have no experience with it.
    – Derek F
    Commented Mar 28, 2022 at 1:56

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .