VBA FORMAT() time function

VBA FORMAT() time function

VBA Format() function can be very usefull for formating date-time values. It can be used as well for extracting date-time parts aside well known functions like Date(), Time(), Now() in SQL query. Let see how we can order Colombo's employees according to their birth days celebration sequence within one year. We passed following values as a second parameter of the function:
"q" - quater of the year (can be 1 to 4)
"m" - month number (can be 1 to 12)
"m" - day of the month number (can be 1 to 31)

SELECT FirstName, LastName,
FORMAT(DateOfBirth, "q" ) AS Quart,
FORMAT(DateOfBirth, "m" ) AS Mon,
FORMAT(DateOfBirth, "d" ) AS [Day],
DateOfBirth
FROM Employees
ORDER BY FORMAT(DateOfBirth, "q" ),
FORMAT(DateOfBirth, "m" ),
FORMAT(DateOfBirth, "d" );

The query returns following results:

FirstName	LastName	Quart	Mon	Day	DateOfBirth
gret	garbo	1	1	15	15/01/49
voich	lutz	1	1	30	30/01/82
aiva	pesh	1	3	10	10/03/85
daniel	defo	2	4	1	01/04/78
dona	fiber	2	5	18	18/05/65
famke	bacher	3	8	26	26/08/66
gina	sohn	3	9	23	23/09/80
...	...	...	...	...	...

We can write allso something like this in order to recive SQLserver like date format:

SELECT FORMAT(Now(), "yyyymmdd ttttt" ) AS ANSI_Date;

Getting:

ANSI_Date

20070821 17:00:54

sqlexamples.info