[SOLVED] Getting the right date

[Brian W]

I need to get the date of last Friday, unless today is Friday in which case get today's date, as a column value. Can't seem to get a grasp on the STR_TO_DATE function or I'm barking up the tree entirely.

SELECT STR_TO_DATE('Friday','%W %M %Y') as `Date` FROM

 

Any help is appreciated. Thanks

 

I wish I could use php to make this date, to bad in this situation I have to some how pass the date already correct.

[roopurt18]

IF( DAYOFWEEK( NOW() )=5, NOW(), NOW() - INTERVAL 7 DAY ) AS `MyValue`

* Not tested...

[Brian W]

hmm, interesting idea. I like it, but it doesn't work.

the - 7 gets whatever today is - 7 days (same day of the week).

I can use some conditional statements if I can figure out how do do something like

..., NOW() + INTERVAL (7 - DAYOFWEEK( NOW() ) DAY

if the day was monday (2), it would work like

7(days in week) - 2 (monday) = 5

2(today) - 5 =  - 3

NOW() + -3 = 3 days ago which from Monday is Friday I think

[roopurt18]

Right, right.  My mistake.

 

IF( 
  DAYOFWEEK( NOW() )=6, 
  NOW(), 
  CASE DAYOFWEEK( NOW() )
    WHEN 1 THEN NOW() - INTERVAL 2 DAY
    WHEN 2 THEN NOW() - INTERVAL 3 DAY
    WHEN 3 THEN NOW() - INTERVAL 4 DAY
    WHEN 4 THEN NOW() - INTERVAL 5 DAY
    WHEN 5 THEN NOW() - INTERVAL 6 DAY
    WHEN 7 THEN NOW() - INTERVAL 1 DAY
  END 
) AS `MyValue

 

Maybe?  I dunno!  I stopped using MySQL years ago.

[Brian W]

Lucky me I don't have to use mysql to get this day anymore!

Thanks guys anyways. I managed to get the other developer to write it in Report Writer.