1

I'm trying to convert a text field to date and only pull back records that have a date of yesterday but getting zero records back. I've converted the text to date part but can't get the where clause working and not sure where I'm going wrong.

SQL Query:

SELECT
REQUEST_DATE,
convert(varchar, REQUEST_DATE ,101) as REQUEST_DATE1
FROM  temp
Where convert(varchar, REQUEST_DATE ,101) > DATEADD(DAY,-1,getdate())
Improve this question
5
  • 1
    Did you mean to post this in a Salesforce specific forum? This looks like it belongs elsewhere in the Stack Exchange network. – Mark Pond Apr 17 '18 at 19:20
  • Yes i'm using the SQL query in SFMC – Peter Ho Apr 17 '18 at 19:21
  • Add the tag [marketing-cloud] next time, more people will see your question that way. Would have added with the first edit, but I was unsure about where you were running the query from. – battery.cord Apr 17 '18 at 19:29
  • You have to convert it to datetime type: CONVERT(datetime, '2017-08-25') – o-lexi Apr 17 '18 at 19:44
  • What's the format of your date string? – Adam Spriggs Apr 17 '18 at 20:11
3

If you just want rows from yesterday, you can do something like this:

select
request_date,
convert(date, request_date) as request_date1
from temp
where convert(date, request_date) >= convert(date, getdate()-1)
and convert(date, request_date) < convert(date, getdate())

If your request_date is actually a date datatype in SFMC, then you can simplify it like this:

select
request_date
from temp
where request_date >= convert(date, getdate()-1)
and request_date < convert(date, getdate())
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.