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())
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
    Commented Apr 17, 2018 at 19:20
  • Yes i'm using the SQL query in SFMC
    – Peter Ho
    Commented Apr 17, 2018 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. Commented Apr 17, 2018 at 19:29
  • You have to convert it to datetime type: CONVERT(datetime, '2017-08-25')
    – o-lexi
    Commented Apr 17, 2018 at 19:44
  • What's the format of your date string? Commented Apr 17, 2018 at 20:11

1 Answer 1

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())

You must log in to answer this question.

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