SQL Server datetimes & FoxPro dates

[Paul Pedersen]

I have a SQL Server table that contains social events that start at a
certain datetime. Is there a clean way to make a view containing the events
that begin on a given day?

In FoxPro, I can just use WHERE TTOD(startdt) = ?whichDate

Is there an equivalent function in SQL Server? If there is, I haven't been
able to find it.

I could add a parameter, to use something like BETWEEN whichDT1 AND
whichDT2, where whichDT2 = DTOT(whichDate + 1), etc. But that would cost me
the goal of avoiding code changes that depend on the back end.


Please don't tell me I have to use DATEPART(year, startdt) = DATEPART(year,
?whichDate) AND DATEPART(month, startdt) = DATEPART(month, ?whichDate) AND
DATEPART(day, startdt) = DATEPART(day, ?whichDate). That would be so
stupid...

[Fred Taylor]

Afraid you'll probably have to live with changes for the backend.

dDateBegin = whichDate
dDateEnd = dDateBegin + 1
SQLEXEC(handle,[... WHERE startdt BETWEEN ?dDateBegin AND ?dDateEnd])

Fred
Microsoft Visual FoxPro MVP

Post by Paul Pedersen
I have a SQL Server table that contains social events that start at a
certain datetime. Is there a clean way to make a view containing the events
that begin on a given day?
In FoxPro, I can just use WHERE TTOD(startdt) = ?whichDate
Is there an equivalent function in SQL Server? If there is, I haven't been
able to find it.
I could add a parameter, to use something like BETWEEN whichDT1 AND
whichDT2, where whichDT2 = DTOT(whichDate + 1), etc. But that would cost me
the goal of avoiding code changes that depend on the back end.
Please don't tell me I have to use DATEPART(year, startdt) =

DATEPART(year,

Post by Paul Pedersen
?whichDate) AND DATEPART(month, startdt) = DATEPART(month, ?whichDate) AND
DATEPART(day, startdt) = DATEPART(day, ?whichDate). That would be so
stupid...

[Anders Altberg]

SQLServer also accepts date as strings in ANSI format
h=SQLCONNECT('pubs')
SQLEXEC(h, "SELECT * FROM Employee WHERE hire_date BETWEEN '1990-04-28
12:00:00' AND '1992-04-28 12:00:00' ", 'crsEmpdate' )
14 rows

-Anders

Post by Paul Pedersen
I have a SQL Server table that contains social events that start at a
certain datetime. Is there a clean way to make a view containing the events
that begin on a given day?
In FoxPro, I can just use WHERE TTOD(startdt) = ?whichDate
Is there an equivalent function in SQL Server? If there is, I haven't been
able to find it.
I could add a parameter, to use something like BETWEEN whichDT1 AND
whichDT2, where whichDT2 = DTOT(whichDate + 1), etc. But that would cost me
the goal of avoiding code changes that depend on the back end.
Please don't tell me I have to use DATEPART(year, startdt) =

DATEPART(year,

Post by Paul Pedersen
?whichDate) AND DATEPART(month, startdt) = DATEPART(month, ?whichDate) AND
DATEPART(day, startdt) = DATEPART(day, ?whichDate). That would be so
stupid...

[Paul Pedersen]

Thanks, guys, for the bad news... :-)