column has only day, want to replace it in complete Date format

[learning_graccess]

I have a table with the three fields (start date, end date, quantity)
start date values are (1,1,3,5,1,2,6)
end date values are (7,7,9,12,7,3,5)
quantity values are (30,40,120,70,15,23,47)

I want to covert the start date values into 1st March 2012, 1st March 2012, 3rd March 2012, 5th March 2012, 1st March, 2012, 2nd March, 2012

Similarly, I want to convert the end date values also like (7th March 2012, 7th March 2012, 9th March 2012, 12th March 2012, 7th march 2012, 3rd March 2012, 5th March 2012)



Is it possible using query?

[AccessMSSQL]

Are your start and end dates always going to be March 2012? How is the data getting into the table?
Query would be like this:
SELECT "March " & [Start Date] & " 2012" AS DateText
FROM YourTableName

But it sounds like you just need to modify your table by either adding a month and a year column, or changes your start and end date fields to a datetime format.

[learning_graccess]

Thanks. But I have got one problem. In [start date] column, some values are blanks. It still writes "march 2012" in such cases. I want to keep them just blanks.

[recyan]

Meanwhile, just check if below gives some guidelines :

SELECT 
	Start_Date, 
	IIf(IsNull([Start_Date]),"",Format(DateSerial(2012,3,[Start_Date]),"mmm d yyyy")) AS NewStartDate
FROM MyTable;

Thanks

[learning_graccess]

It worked. Thanks mate..

[learning_graccess]

But when I try to update the above in the table, it shows error

update table2 set [start date] = iif(ISNULL([start date]),"",FORMAT(DATESERIAL(2012,3,[start date]),"dd-mm-yyyy"))