If you’re using Foxfire! with SQL Server, you have likely realized that SQL Server has different functions for dates than regular DBF files. You can’t simply use DATE() or DATE()+5 for date expressions, instead you need to use SQL Server specific functions.
We’ll be updating this post with additional items.
| Usage | DBF | SQL Server |
| Today’s Date | DATE() | Getdate() |
| Tomorrow | DATE()+1 | Getdate()+1 |
| Day of Week (as a number) | DOW(DATE()) | DATEPART(dw,getdate()) |
| Start of the current week | DATE()-DAY(DATE())+1 | DATEADD(month,DATEDIFF(month,0,getdate()),0) |
| Start of the current month | DATE()-DAY(DATE())+1 | DATEADD(month,DATEDIFF(month,0,getdate()),0) |
| End of current month | GOMONTH(DATE()-DAY(DATE())+1),1) | DATEADD(MONTH,1,DATEADD(month,DATEDIFF(month,0,getdate()),0))-1 |
| Start of Previous Quarter | DATEADD(qq,DATEDIFF(qq,0,getdate())-1,0) | |
| End of Previous Quarter | DATEADD(dd,-1,DATEADD(qq,DATEDIFF(qq,0,getdate()),0)) |
The DATEPART function in T-SQL is really helpful for a lot of other calculations. Here is a list of the different terms you can use with DATEPART.
d – day
dw – day of week
m,mm,month – month
q – quarter (based on Jan 1 being quarter 1)
yyyy – year
Note that when using DBF, DATE() is the current date and DATETIME() gives you a Date and Timestamp. Using + with timestamps increases the value by seconds, not days. In SQL, getdata() returns a timestamp BUT if you use GETDATE()+1, you will get today’s date + one day.
There are lots of sites that describe other common date functions, including stackoverflow and the SQL Server administration blog.