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.