Is it possible to pass datepart abbreviations to a function?

[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()))