Change a datefield to the first day in the given month?

[Mikael Lindqvist]

Hi,

I have a datefield that contains dates (yyyy-mm-dd).

Now, I want to convert this date field so that they DAY (dd) is changed to
the first day in the month (01)...

Example of data (before and after)
2008-12-05 -> 2008-12-01
2008-11-07 -> 2008-11-01
2008-10-27 -> 2008-10-01

I'm sure this can't be too complicated, but I just can't figure out how
(except converting it to a text field then extract yyy-mm and add "01" and
convert it back to date format, but I rather use a datefunction if that's
possible).

Kindly,
Mikael

 

[John Spencer (MVP)]

One way
DateSerial(Year([DateField]),Month([DateField]),1)

Another way
DateAdd("d",1-Day([DateField]),[DateField])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Tom van Stiphout]

On Fri, 23 Jan 2009 00:40:01 -0800, Mikael Lindqvist

Use the DateSerial function:
select DateSerial(Year(myDate), Month(myDate),1)
from myTable
(of course you need to replace myObjectNames with yours)

-Tom.
Microsoft Access MVP

 

[Marshall Barton]

I have a datefield that contains dates (yyyy-mm-dd).

Now, I want to convert this date field so that they DAY (dd) is changed to
the first day in the month (01)...

DateSerial(Year(datefield ), Month(datefield), 1)

 

[fredg]

Hi,

I have a datefield that contains dates (yyyy-mm-dd).

Now, I want to convert this date field so that they DAY (dd) is changed to
the first day in the month (01)...

Example of data (before and after)
2008-12-05 -> 2008-12-01
2008-11-07 -> 2008-11-01
2008-10-27 -> 2008-10-01

I'm sure this can't be too complicated, but I just can't figure out how
(except converting it to a text field then extract yyy-mm and add "01" and
convert it back to date format, but I rather use a datefunction if that's
possible).

Kindly,
Mikael

If you are just wishing to display the date as of the 1st of the
month, you can use:
exp: Format([ADate],"yyyy-mm-01")

 

[Rick Brandt]

The DateSerial(Year, Month, Day) function is very handy for this because
with it the zeroth day of a month is equal to the last day of the
previous month. So in your case...

=DateSerial(Year(YourField), Month(YourField)+1, 0)

And yes this handles year-end wrap-around, leap year and any other issue
you might think of just fine.

Sorry, I somehow looked at your request for the first of the month and in
my head it became last of the month. The other responders have given the
correct solution.