[SalesForce] SQL convert text to date and minus 1day ago query

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

Best Answer

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

Related Solutions

[SalesForce] Marketing Cloud Query activity using DateAdd() to get records

You'll need to consider the time portion of the dates. Here's a sargable way to do it:

SELECT * 
FROM [UG Journey Master]
WHERE [dateofApplication] >= DateAdd(day,-15,cast(GetDate() as date))
and [dateofApplication] < DateAdd(day,-14,cast(GetDate() as date))

Casting getDate() as a date, will reset the time part of the date to zero (midnight).

Update (20170804)

Here's a more efficient way to do it:

SELECT * 
FROM [UG Journey Master]
WHERE [dateofApplication] >= convert(date,getDate()-15)
and [dateofApplication] < convert(date,getDate()-14)

DATE function to get past 60 days of data

Moving from comment to answer for easier reference:

With dates, greater than means more recent. So you may have your operators backwards.

If you want any records 60 days or more prior to today, you will need to do Convert(date, InstallDate) <= Convert(date, getDate() - 60)

and then do >= for when you want 60 days or less prior to today(e.g. the last 60 days worth of data).

Best Answer

Related Solutions

[SalesForce] Marketing Cloud Query activity using DateAdd() to get records

DATE function to get past 60 days of data

Related Topic