Date conversion query

[mephinzero]

Hello, I need to create a query that would convert my data to the needed format:

Currently I have two date columns in the table which are Quarter ( "Q1","Q2","Q3","Q4") and Year ("2000","2001","2003"...).

I want a query that would convert these numbers into actual dates. I want every Q1 to be equal 3/31/****, every Q2 - 6/30/****, every Q3 - 9/30/****, and every Q4 - 12/31/**** .

And suggest me options on how to do it in the database please. Should I have another "Date" field in the same table in order for these dates to be inserted there? Or should it be something else.

Thanks in advance for the help

 

[recyan]

Just check out if below gives some guidelines :

SELECT 
	myTable.theQuarter, 
	myTable.theYear, 
	IIf([theQuarter]="Q1",3,IIf([theQuarter]="q2",6,IIf([theQuarter]="Q3",9,12))) AS TheMonth, 
	DateSerial([theYear],[TheMonth]+1,1) AS TheNextMonthFirstDate, 
	DateAdd("d",-1,[TheNextMonthFirstDate]) AS TheRequiredDate
FROM myTable;

Thanks

 

[mephinzero]

Just check out if below gives some guidelines :

SELECT 
    myTable.theQuarter, 
    myTable.theYear, 
    IIf([theQuarter]="Q1",3,IIf([theQuarter]="q2",6,IIf([theQuarter]="Q3",9,12))) AS TheMonth, 
    DateSerial([theYear],[TheMonth]+1,1) AS TheNextMonthFirstDate, 
    DateAdd("d",-1,[TheNextMonthFirstDate]) AS TheRequiredDate
FROM myTable;

Thanks

Thanks a lot recyan,
It works exactly as I wanted.

 

[recyan]

Glad you found it helpful.

Thanks.