topic Re: date mysql in SAS Programming

date mysql

mansour_ibrahim — Thu, 20 Jul 2017 15:57:35 GMT

Hello,

I have requests mysql, i want to translate in sas :

1/ date> DATE_FORMAT(now(), "%Y-%m-01")

i make this ==>> date> intnx('month',today(),0,'b') ; is't correct ???

2/ MOIS >= DATE_FORMAT( DATE_SUB( NOW( ) , INTERVAL 1 MONTH) , '%Y-%m-01');

i make this ===>> mois > INTNX('month',today(),1,'b'); is't correct ???

Thank you

Re: date mysql

Reeza — Thu, 20 Jul 2017 16:06:02 GMT

@mansour_ibrahim wrote:

Hello,

I have requests mysql, i want to translate in sas :

1/ date> DATE_FORMAT(now(), "%Y-%m-01")

i make this ==>> date> intnx('month',today(),0,'b') ; is't correct ???

2/ MOIS >= DATE_FORMAT( DATE_SUB( NOW( ) , INTERVAL 1 MONTH) , '%Y-%m-01');

i make this ===>> mois > INTNX('month',today(),1,'b'); is't correct ???

Thank you

Are you getting the values you expect?

Pretend we dont' know MYSQL, what are you trying to do here, in words?

Re: date mysql

mansour_ibrahim — Thu, 20 Jul 2017 16:13:31 GMT

what are you trying to do here, in words? I do not know for the moment

Re: date mysql

Tom — Fri, 21 Jul 2017 02:20:26 GMT

@mansour_ibrahim wrote:

what are you trying to do here, in words? I do not know for the moment

Then how will you know whether you have gotten the right answer?

Perhaps you need to post a question on a MySQL site to find out the meaning of the original syntax?

Re: date mysql

Tom — Fri, 21 Jul 2017 03:01:06 GMT

As best as I can tell from this man page https://www.w3schools.com/sql/func_mysql_date_format.asp the meaning of DATE_FORMAT() is more like a FORMAT statement than like an actual function. So something like

select DATE_FORMAT(now(), "%Y-%m-01") as mydatevar
     , DATE_FORMAT( DATE_SUB( NOW( ) , INTERVAL 1 MONTH) , '%Y-%m-01') as mydatevar2

would translate into creating a variable with today's date but with a format attached to it that displays it as if it was the first day of the month. You could possible build such a format, but why not just use one of the existing date formats that does NOT display the day of the month so that it doesn't matter what the day of the month really is? The DATE_SUB() function subtracts intevals dates so you need to use INTNX() to simulate that.

select date() as mydatevar format=yymmd7.
     , intnx('month',date(),-1,'s') as mydatevar2 format=yymmd7.

But it might just be easier to just set the value to the first day of the month instead then you could use the YYMMDDd10. format (the last D means to use dash as separator) to have it display that date in YYYY-MM-DD format.

select intnx('month',date(),0,'b') as mydatevar format=yymmddd10.
     , intnx('month',date(),-1,'b') as mydatevar2 format=yymmddd10.

Note that you need to use a negative number in your INTNX() calls since DATE_SUB() means SUBTRACT. There is a separate function, DATE_ADD(), that is used to move forward in time.

Re: date mysql

Reeza — Fri, 21 Jul 2017 02:46:13 GMT

@mansour_ibrahim wrote:

what are you trying to do here, in words? I do not know for the moment

Whenever I'd say that as a kid, my dad would respond, "If you don't know, who should?" 🙂