Round datetime to nearest minute or hour

declare @dt datetime
set @dt = '09-22-2007 15:07:38.850'
select dateadd(mi, datediff(mi, 0, @dt), 0)
select dateadd(hour, datediff(hour, 0, @dt), 0)

will return

2007-09-22 15:07:00.000
2007-09-22 15:00:00.000

The above just truncates the seconds and minutes, producing the results asked for in the question. As @OMG Ponies pointed out, if you want to round up/down, then you can add half a minute or half an hour respectively, then truncate:

select dateadd(mi, datediff(mi, 0, dateadd(s, 30, @dt)), 0)
select dateadd(hour, datediff(hour, 0, dateadd(mi, 30, @dt)), 0)

and you'll get:

2007-09-22 15:08:00.000
2007-09-22 15:00:00.000

---

Before the date data type was added in SQL Server 2008, I would use the above method to truncate the time portion from a datetime to get only the date. The idea is to determine the number of days between the datetime in question and a fixed point in time (0, which implicitly casts to 1900-01-01 00:00:00.000):

declare @days intset @days = datediff(day, 0, @dt)

and then add that number of days to the fixed point in time, which gives you the original date with the time set to 00:00:00.000:

select dateadd(day, @days, 0)

or more succinctly:

select dateadd(day, datediff(day, 0, @dt), 0)

Using a different datepart (e.g. hour, mi) will work accordingly.

http://stackoverflow.com/questions/6666866/t-sql-datetime-rounded-to-nearest-minute-and-nearest-hours-with-using-functions

SSIS Flat File skips importing the last row

When copying an SSIS file from one server to another, the text qualifier for flat files gets messed up. It becomes _x003C_none_x003E_ instead of <none>. This causes the last record of the flat file to be skipped when importing.

SET ROWCOUNT

---------------------------
Microsoft SQL Server Management Studio
---------------------------
No row was updated.


The data in row 2 was not committed.
Error Source: Microsoft.SqlServer.Management.DataTools.
Error Message: The row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows).


Correct the errors and retry or press ESC to cancel the change(s). 
---------------------------
OK   Help   
---------------------------

To workaround this error without modifying the table structure by adding an identity column, use the "SET ROWCOUNT" command.

SET ROWCOUNT 1

update b set column1 = 1

FROM         Table b

WHERE   column2 = 'ABC'

NOTE: The ROWCOUNT statement setting is used for the entire duration of the connection!