Extracting The Date From a SQL DateTime In SQL Queries

Ever wondered how to quickly and easily get the date portion of a datetime field in a SQL query? Seems like it should be easy, right? Unfortunately, it isn’t. I’ve found a lot of solutions, but this one is the most elegant I’ve seen.

CONVERT(datetime, FLOOR(CONVERT(float, DateTime)))

I’ve put this into a user-defined function below:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ExtractDate]
(
@DateTime datetime
)
RETURNS datetime
AS
BEGIN
RETURN CONVERT(datetime, FLOOR(CONVERT(float, @DateTime)))
END

This can be called like so:

SELECT dbo.ExtractDate(DateTime) FROM Table

Credit for this conversion method goes to Matt Berseth, i stumbled across it in the example code for something else.

SQL Server 2005 Service Broker Error Code 25 (Event ID 28054)

Another issue I’ve run into with the service broker and moving databases between servers is this error message:

Error code:25. The master key has to exist and the service master key encryption is required.

The solution that I found was the regenerate the key for the database using the following code:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘1ComplexPassword!’

You might also need to run the following afterwards:

ALTER DATABASE dbname SET NEW_BROKER.

Also, see my previous post SQL Server 2005 Service Broker Error 15517 for another error message and solution.