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
SET QUOTED_IDENTIFIER ON
CREATE FUNCTION [dbo].[ExtractDate]
RETURN CONVERT(datetime, FLOOR(CONVERT(float, @DateTime)))
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.
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.
Random development on many topics by Brant Burnett