select first and last day in a month

[sal21]

In a field DATA1 have a list of days:

01/01/2014
02/01/2014
...
31/01/2014
02/02/2014
03/02/2014
...
15/03/2014
02/03/2014
03/03/2014
15/03/2014

i need to calculate via sql (to use in vb6) max day from each month...

final result:

31/01/2014
03/02/2014
15/03/2014

note:
- i can have duplicates date in filed
- naturally i need also an example to extract the min day

[HansV]

You can use

SELECT Max(DATA1) AS MaxDate FROM MyTable GROUP BY DATA1-Day(DATA1)+1

where MyTable is the name of the table.

[sal21]

You can use

SELECT Max(DATA1) AS MaxDate FROM MyTable GROUP BY DATA1-Day(DATA1)+1

where MyTable is the name of the table.

ok work perfect!

ops....

but now if i fill my_var (date dimensioned) with a value from list, for example 02/01/2014, how to exract the max day from the dates in field DATA1, in this case 31/01/2014.

Always with a sql, please.

[HansV]

Sorry, I don't understand what you want.

[sal21]

Sorry, I don't understand what you want.

Myvar=02/01/2014
Extract From The List of dates in Field The max date based month in myvar in My case 31/01/2014.

[HansV]

Code: Select all

    strSQL = "SELECT Max(DATA1) AS MaxDate FROM MyTable GROUP BY DATA1-Day(DATA1) " & _
        "HAVING DATA1-Day(DATA1)=#" & Format(MyVar - Day(MyVar), "yyyy-mm-dd") & "#"

[sal21]

Code: Select all

    strSQL = "SELECT Max(DATA1) AS MaxDate FROM MyTable GROUP BY DATA1-Day(DATA1) " & _
        "HAVING DATA1-Day(DATA1)=#" & Format(MyVar - Day(MyVar), "yyyy-mm-dd") & "#"

WORK!

But i need to insert a where clausole one different field in wich part of query?

[HansV]

What do you mean?

[HansV]

Sal, it's better to post a new reply than to edit an existing reply. If a thread has a new reply, I will see it, but not if a reply has been edited.

Perhaps you can use something like this:

Code: Select all

    strSQL = "SELECT Max(DATA1) AS MaxDate FROM MyTable WHERE OtherField='" & _
        OtherVar & "' GROUP BY DATA1-Day(DATA1) " & _
        "HAVING DATA1-Day(DATA1)=#" & Format(MyVar - Day(MyVar), "yyyy-mm-dd") & "#"

where OtherField is the field you want to impose a condition on, and OtherVar is the value you want to restrict it to. In this example, OtherVar is a text field. If it is a number field, omit the ' characters.