Splitting Date&Time field

[swift]

I need to separate a date/time field held in my database into a date field and a time field. I want to do this so I can query the number of records by date. I’ve looked at the various options mentioned on the forum, including the LEN, DateValue, Format functions etc but with no success so far unfortunately!

This is the SQL for the query so far (the field I want to split is DFDateRaised):

SELECT SectionData.RouteNumber, DefectData.CompClassTag, DefectData.DFDateRaised, DefectData.RoomDirnTitle, DefectTypes.NewCategory
FROM ((SectionData INNER JOIN DefectData ON SectionData.SectionLabelRMMS = DefectData.[Section Ref]) INNER JOIN DefectTypes ON DefectData.DPCode = DefectTypes.ExistingCategory) LEFT JOIN Months ON DefectData.DFDateRaised = Months.DefectDate
WHERE (((DefectData.DPCode)<>"As New" And (DefectData.DPCode)<>"Cyclic" And (DefectData.DPCode)<>"None" And (DefectData.DPCode)<>"Satis" And (DefectData.DPCode)<>"Unspec" And (DefectData.DPCode)<>"New"))
ORDER BY DefectData.[Section Ref];

This is the format of the date and time field: 08/05/2009 09:46:00

If anyone can help I would be most grateful!!

Cheers

swifty

 

[Brianwarnock]

You don't need to split the date/time, just
Format(DFDateRaised,"DD/MM/YYYY") or whatever your format is mm/dd/yyyy maybe will cause the action to operate only on the date portion, unless I've misunderstood what you are trying to do, ie count records per day.

Brian

 

[swift]

OK, thanks for that.

I've tried the Format([DFDateRaised],"dd/mm/yyyy") in the criteria box in the query grid, under the DFDateRaised field, and it is returning zero results.

I think perhaps I've phrased my original question incorrectly. I'll try to explain further. At this stage I just need to separate out (or not, as the case may be) the date from the date/time field as I wish to return a value of month/year based on the date, eg DFDateRaised is 05/11/2007 = November2007, DFDateRaised is 22/04/2009 = April2009 etc etc.

To do this I've got a list of all dates (in the dd/mm/yyyy format) from the past three years with a month/year value corresponding to it. However I'm importing from Excel and obviously its giving me date/time field which won't compare. Hence the need to show only date.

Then I'm going to group on month etc for reporting purposes. This last bit I'm confident of doing ok, I've done similar before.

I hope thats clearer. I have a feeling that someones going to come along and say ' what are you doing it like that for'!!! Any help gratefully received!!

Cheers

swifty

 

[MyTech]

Insert a Field in the query.


In the first line enter the following text (without the outside quotes):

To get the DATE of a M/D/Y field:
"Date: CDate(Month([DFDateRaised]) & "/" & (Day([DFDateRaised]) & "/" & (Year([DFDateRaised]))))"


To get the DATE of a D/M/Y field:
"Date: CDate(Day([DFDateRaised]) & "/" & (Month([DFDateRaised]) & "/" & (Year([DFDateRaised]))))"


To get the TIME:
"Time: CDate(Hour([DFDateRaised]) & ":" & (Minute([DFDateRaised]) & ":" & (Second([DFDateRaised]))))"

Did it help?

 

[swift]

Yes thats brilliant, thanks everyone for your help - I knew someone would come along with a better suggestion than my cobbled together effort!!!

One thing would be good though, how would I return the month as 'October' instead of '10'? Any ideas?

Cheers muchly

swifty

 

[MyTech]

Let's take the M/D/Y for example.

Add Before the expression:
"Format("

Add after the expression:
","mmmm/dd/yyyy)""


Date: Format(CDate(Month([DFDateRaised]) & "/" & (Day([DFDateRaised]) & "/" & (Year([DFDateRaised])))),"mmmm/dd/yyyy")

 

[MyTech]

Month: =Format(Month([DFDateRaised]),"mmmm")

Date: =Format(CDate(Month([DFDateRaised]) & "/" & (Day([DFDateRaised]) & "/" & (Year([DFDateRaised])))),"mmmm/dd/yyyy")

 

[Brianwarnock]

OK, thanks for that.

I've tried the Format([DFDateRaised],"dd/mm/yyyy") in the criteria box in the query grid, under the DFDateRaised field, and it is returning zero results.

I think perhaps I've phrased my original question incorrectly. I'll try to explain further. At this stage I just need to separate out (or not, as the case may be) the date from the date/time field as I wish to return a value of month/year based on the date, eg DFDateRaised is 05/11/2007 = November2007, DFDateRaised is 22/04/2009 = April2009 etc etc.


swifty

It doesn't go in the criteria, to achieve what you want all you need to do is Group on
Month_Year: Format([DFDateRaised],"mmmm/yyyy"), but note that the order will be by month(alphabetic) and year

Brian