Sql Date To String

[abq47]

Produce a list of all employees and their date of hire (HireDate) in the format "ALLEN was hired on the "22nd of Feburary."




emp table


EmpNo Ename Job Mgr HireDate Sal Comm
7369 SMITH CLERK 7902 6/7/1980 800
7499 ALLEN SALESMAN 7698 2/2/1981 1600 300
7521 WARD SALESMAN 7698 2/6/1981 1250 500
7566 JONES MANAGER 7839 4/3/1981 2975

Months Table

Month MonthName
1 January
2 February
3 March
4 April
5 May
6 June




Ordinals Table


DayNumber DayOrdinal
1 1st
2 2nd
3 3rd
4 4th
5 5th
6 6th
7 7th

[debasisdas]

Kindly post what you have tried to get the desired output.

[FishVal]

Hi, there.

Join the tables:
[emp table] and [Months Table] on Month([emp table].[HireDate])=[Months Table].[Month]
and the resulting dataset with [Ordinals Table] on Day([emp table].[HireDate])=[Ordinals Table].[DayNumber]

From thus obtained dataset retrieve calculated field like the following:
[code=sql]
[emp table].[Ename] & ' was hired on ' & [Ordinals Table].[DayOrdinal] & ' of ' [Months Table].[MonthName]
[/code]

BTW, in Access you may not use [Month Table] at all as soon as Format() function can retrieve month name (either full or 3-letters signature) from date.

Regards.
Fish

P.S. [Ordinals Table] may be omitted too using simple logic like the following
[code=sql]
Day([emp table].[HireDate]) & Nz(Switch(Day([emp table].[HireDate]) Mod 10 = 1, "st", Day([emp table].[HireDate]) Mod 10 = 2, "nd", Day([emp table].[HireDate]) Mod 10 = 3, "rd"), "th")
[/code]

[NeoPa]

This is a "Homework" question. I am locking it now.

If, by any chance, it is not homework, then it's clearly a lazily and disrespectfully posted question.

Either way, if you want help then state your question clearly and say what you've done to try to resolve it yourself (FAQ - How to Ask a Question). This can now be done in a new (separate) thread.