DATA Step, Macro, Functions and more

date mysql

Reply
Occasional Contributor
Posts: 19

date mysql

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

 

Super User
Posts: 19,777

Re: date mysql

Posted in reply to mansour_ibrahim

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?

Occasional Contributor
Posts: 19

Re: date mysql

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

Super User
Super User
Posts: 7,039

Re: date mysql

Posted in reply to mansour_ibrahim

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?

Super User
Posts: 19,777

Re: date mysql

Posted in reply to mansour_ibrahim

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?" Smiley Happy

Super User
Super User
Posts: 7,039

Re: date mysql

[ Edited ]
Posted in reply to mansour_ibrahim

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.

 

 

Ask a Question
Discussion stats
  • 5 replies
  • 135 views
  • 0 likes
  • 3 in conversation