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

  • Thread starter Thread starter Mikael Lindqvist
  • Start date Start date
M

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
 
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
 
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
 
Mikael said:
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)...
Click to expand...


DateSerial(Year(datefield ), Month(datefield), 1)
 
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
Click to expand...

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")
 
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.
Click to expand...

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.
 
Back
Top