Is it possible to pass datepart abbreviations to a function?

fd9750

Hi Everyone, First: what is desired Most of us probably know this trick for clipping of the time part of a DATETIME value (at least you can find it in an awful lot of places on the internet): @Date = DATEADD (DD, DATEDIFF (DD, 0, @Date), 0) It will simply get you a new DATETIME value with the time set at 00:00:00. If you use any other datepart abbreviation you get the same but with the date or time clipped at whatever you selected. Example: if you use MI you get your DATETIME clipped at the minute value (i.e: seconds and milliseconds set to 0). It is very useful but it would be even more useful if it were possible to put this in a function. Then it would be possible to call "my_f_cliptime (XXX, DateTimeValue)" in which XXX (note the lack of quotes) would be the datepart abbreviation you want to pass to the function for further use within the DATEADD and DATEDIFF operations. The problem is that DATEADD and DATEDIFF do support datepart abbreviations but you have to include them in your source code directly. Also: you can't pass those abbreviation as strings because DATEADD and DATEDIFF do not accept them that way. So here is the question: does anyone know of a way to pass the datepart abbreviations (there does not seem to be a datepart variable type) or, alternatively, a way to convert equivalent strings to datepart abbreviations before passing them to DATEADD and DATEDIFF ? For the moment I suppose there is'nt but then again you never know. Bye PS: I am generally not very curious but if possible I prefer knowing everything

J4amieC

According to This SO answer[^] there is no way to paramterize this argument, so you're stuck with CASE logic Something like this would do nicely:

CREATE FUNCTION dbo.FlooredDate
(
@date DATETIME,
@interval VARCHAR(10)
)
RETURNS DATETIME
AS
BEGIN

RETURN CASE @interval
	WHEN 'DD' THEN DATEADD (DD, DATEDIFF (DD, 0, @Date), 0)
	WHEN 'MI' THEN DATEADD (MI, DATEDIFF (MI, 0, @Date), 0)
	/\* etc... \*/
END

END
GO

fd9750

Too bad, I did not see this particular answer before but it confirms a number of similar others I have seen. I knew I could do it with a case structure like that but I wanted to do it with less code if it was possible. It just goes to show that you can easily want it all but getting it all is a lot tougher.

PIEBALDconsult

I wrote a function:

CREATE FUNCTION [Lib].[DateTruncate] (@Subject DateTime , @Granularity varchar(4))
RETURNS DateTime AS
BEGIN
DECLARE @DW int
DECLARE @DF int

SET @DW = DatePart(dw,@Subject) - 1

IF @Granularity = 'YY'
BEGIN
    SET @Subject = dateadd(mm,-datepart(mm,@Subject)+1,@Subject)
    SET @Granularity = 'MM'
END

IF @Granularity = 'MM'
BEGIN
    SET @Subject = dateadd(dd,-datepart(dd,@Subject)+1,@Subject)
    SET @Granularity = 'DD'
END

IF @Granularity = 'SU'
BEGIN
    SET @DF = 7-@DW
    IF @DF >= 0 SET @DF = @DF-7
    SET @Subject = dateadd(dw,@DF,@Subject)
    SET @Granularity = 'DD'
END

IF @Granularity = 'MO'
BEGIN
    SET @DF = 1-@DW
    IF @DF >= 0 SET @DF = @DF-7
    SET @Subject = dateadd(dw,@DF,@Subject)
    SET @Granularity = 'DD'
END

IF @Granularity = 'TU'
BEGIN
    SET @DF = 2-@DW
    IF @DF >= 0 SET @DF = @DF-7
    SET @Subject = dateadd(dw,@DF,@Subject)
    SET @Granularity = 'DD'
END

IF @Granularity = 'WE'
BEGIN
    SET @DF = 3-@DW
    IF @DF >= 0 SET @DF = @DF-7
    SET @Subject = dateadd(dw,@DF,@Subject)
    SET @Granularity = 'DD'
END

IF @Granularity = 'TH'
BEGIN
    SET @DF = 4-@DW
    IF @DF >= 0 SET @DF = @DF-7
    SET @Subject = dateadd(dw,@DF,@Subject)
    SET @Granularity = 'DD'
END

IF @Granularity = 'FR'
BEGIN
    SET @DF = 5-@DW
    IF @DF >= 0 SET @DF = @DF-7
    SET @Subject = dateadd(dw,@DF,@Subject)
    SET @Granularity = 'DD'
END

IF @Granularity = 'SA'
BEGIN
    SET @DF = 6-@DW
    IF @DF >= 0  SET @DF = @DF-7
    SET @Subject = dateadd(dw,@DF,@Subject)
    SET @Granularity = 'DD'
END

IF @Granularity = 'DD'
BEGIN
    SET @Subject = dateadd(hh,-datepart(hh,@Subject),@Subject)
    SET @Granularity = 'HH'
END

IF @Granularity = 'HH'
BEGIN
    SET @Subject = dateadd(mi,-datepart(mi,@Subject),@Subject)
    SET @Granularity = 'MI'
END

IF @Granularity = 'MI'
BEGIN
    SET @Subject = dateadd(ss,-datepart(ss,@Subject),@Subject)
    SET @Granularity = 'SS'
END

IF @Granularity = 'SS'
BEGIN
    SET @Subject = dateadd(ms,-datepart(ms,@Subject),@Subject)
END

RETURN @Subject

END

(Hmmm... I thought it

J4amieC

The OP was looking for less code. :omg:

jschell

You can pass in with a text literal and then use dynamic SQL to invoke it. Probably less performant, perhaps less safe, but with less code.

Mycroft Holmes

Turn the kludge into a UDF to hide the bulk code and then you have a nice neat function to call. Especially as this feels like it should be reusable code.

Never underestimate the power of human stupidity RAH

PIEBALDconsult

I don't know that language.

J4amieC

errr... thats exactly what my example had.

Mycroft Holmes

Then you sir deserve 5, I was responding to his too much code comment :-O without actually registering the details of your response otherwise I'd have given credit where it was due!

Never underestimate the power of human stupidity RAH

J4amieC

Why, thank you :)

J4amieC

Filip Dossche wrote:

but I wanted to do it with less code if it was possible

Just to be clear (it wasn;t explicit from my original post), that I had shown the code for a User Defined Function (UDF), and therefore this code would be written once, and not repeated every time you needed this logic. Thereafter, the usage would be as simple as

SELECT * FROM Whatever WHERE date>dbo.FloorDate('DD',GetDate())

or perhaps

INSERT INTO somewhere (someInfo,someDate) VALUES ('abc',dbo.FloorDate('DD',GetDate()))