Date from db

[ziggy_909]

 

i need to get dates from my database and return the dates as the 1st of the previous month...

 

ie 2010-12-17

will return as 2010-11-01

 

any ideas please....

[mmarif4u]

Do you mean, every 1st date of the month?

OR please explain more clearly.

[fortnox007]

I bet it can be found here: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

month() or monthname() i think

[ziggy_909]

Do you mean, every 1st date of the month?

OR please explain more clearly.

 

for the returned date from the db .. i need to display it in a table as the 01-previous month- same year

 

eg returned date 2009-12-24 is displayed as 2009-11-01

    returned date 2010-06-02 is displayed as 2010-05-01

 

[mmarif4u]

Try the link fortnox007 posted above, its a rich source of date functions.

If still can't figure it out, post the code u tried.

[ziggy_909]

Try the link fortnox007 posted above, its a rich source of date functions.

If still can't figure it out, post the code u tried.

 

DATE_SUB(tbl_col, INTERVAL 1 MONTH),

 

this will remove 1 month from the date...

 

date_sub(tbl_co, interval day(tbl_col)-1 day)

 

this will display date as 1st day of the month

 

is there a way to combine both?

[Pikachu2000]

This should get you started.

SELECT DATE_FORMAT(DATE_SUB(tbl_col, INTERVAL 1 MONTH), '%Y-%m-01') FROM table

[ziggy_909]

This should get you started.

SELECT DATE_FORMAT(DATE_SUB(tbl_col, INTERVAL 1 MONTH), '%Y-%m-01') FROM table

 

thanks that worked a treat... another quick question?

 

is there a way to return the first working day of the month ie if the 01st is a sunday return 02nd instead