Cdate Function

[paulmitchell507]

Can anyone explain why when use the following function in an Access
2000 query the results are dd/mm/yy?
CDate(Str(Month([mydate])) & "-1-" &
Str(Year([mydate]))) "

An example would be, [mydate] is stored in the table as dd/mm/yyyy,
21/02/2008. I would like the function to set the dd part of the date
to 01 and leave the mm/yyyy as it is. The output I get is 21/01/2008.

Regards,

Paul

 

[vbasean]

Can anyone explain why when use the following function in an Access
2000 query the results are dd/mm/yy?
CDate(Str(Month([mydate])) & "-1-" &
Str(Year([mydate]))) "

An example would be, [mydate] is stored in the table as dd/mm/yyyy,
21/02/2008.  I would like the function to set the dd part of the date
to 01 and leave the mm/yyyy as it is. The output I get is 21/01/2008.

Regards,

Paul

how about this

dateadd("d", -(datepart("d",mydate)-1),mydate)

 

[bcap]

http://support.microsoft.com/kb/210604/en-us

 

[BruceM]

You may do better with DateSerial:
DateSerial(Year([mydate]),Month([mydate],1)

A date is stored in the table as a number (of type Double, I think) in which
the integer portion represents the days that have elapsed since Dec. 31,
1899, and the decimal portion represents the time of day. You applied a
format to the table, but then you used CDate, which applies Control Panel
regional settings unless you specify otherwise. It doesn't care about the
format applied elsewhere to the date. Had you simplified it to:
Month([mydate]) & "-1-" & Year([mydate]) you would have obtained something
close to the expected result, except Month would have been a single digit:
2-1-2008
You could have used Format to obtain the desired format:
Format([mydate],"mm") & "-1-" & Year([mydate])
but I still think DateSerial would be simpler.
As it was it looks as if CDate tried to interpret the value according to
your Control Panel regional settings, and decided it was the 21st day of the
current month, or something like that.

 

[paulmitchell507]

Can anyone explain why when use the following function in an Access
2000 query the results are dd/mm/yy?
CDate(Str(Month([mydate])) & "-1-" &
Str(Year([mydate]))) "

An example would be, [mydate] is stored in the table as dd/mm/yyyy,
21/02/2008.  I would like the function to set the dd part of the date
to 01 and leave the mm/yyyy as it is. The output I get is 21/01/2008.

Regards,

Paul

how about this

dateadd("d", -(datepart("d",mydate)-1),mydate)

Thank you vbsean, that is just what I needed.

Regards