I'm trying to write an SQL Query in SQL Server 2008 to update a date
field in my table. The field is a DATETIME field type. When a record
is inserted and it is over midnight (lets' say between MIDNIGHT 00:00
AM AND 03:00 AM, i want to store the previous Date.

Any help would be appreciated. Thanks in ADVANCE

Fiaola (teeleaf96799 (AT) gmail (DOT) com) writes:
The dateadd function might help you. Unfortunately, I don't want to say
more than so, because I don't understand what you are looking for. Could
you give an example with the starting point and what you want the final
result to be?


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Fiaola wrote:
If you are only interesting in storing the date portion, then you could
use this:

CREATE TABLE #t (my_datetime_column datetime)
INSERT INTO #t VALUES ('20101030 11:00')
INSERT INTO #t VALUES ('20101027 02:30')
INSERT INTO #t VALUES ('20101021 05:00')
INSERT INTO #t VALUES ('20101015 23:59')

SELECT my_datetime_column
, DATEADD(day, DATEDIFF(day, 0, DATEADD(hour, -3,
my_datetime_column)), 0)
FROM #t

DROP TABLE #t

--
Gert-Jan