In the information jungle for Excel, when it comes to find certain “How-to” with Excel, it is like finding a “needle in Mount Everest”.
Here is the list of functions that you can use when using ODBC + SQL + Excel
Again, the following stuff applies only when using SQL with Excel over ODBC connection.
One of the important function is Dateserial which converts to a date type given the year, month and day.
|
|
|
|
.
|
Excel date function |
string |
|
.
|
Last Day of the month |
dateserial(year(now()),month(now())+1,0) |
|
.
|
First Day of month |
dateserial(year(now()),month(now()),1) |
|
.
|
First day of year |
dateserial(year(now()),1,1) |
|
.
|
Last day of year |
dateserial(year(now()),13,0) |
|
.
|
Week of year |
datepart(’ww’,now()) |
|
.
|
Week Day |
datepart(’w',now()) |
|
.
|
Quarter of year |
datepart(’q',now()) |
|
.
|
First day of Quarter |
dateserial(year(now), (datepart(’q',now())-1)*3+1 ,1 ) |
|
.
|
Last day of quarter |
dateserial(year(now), datepart (’q',now() ) *3+1 ,0 ) |
|
.
|
First day of week |
now() – datepart(’w',now())+1 |
|
.
|
Last day of week |
now() + 7- datepart(’w',now()) |
|
More about the DatePart and DateSerial functions
| Interval |
Explanation |
| yyyy |
Year |
| q |
Quarter |
| m |
Month |
| y |
Day of year |
| d |
Day |
| w |
Weekday |
| ww |
Week |
| h |
Hour |
| n |
Minute |
| s |
Second |
Feb
2
