Datetrim for datetimes
Multi tool use
Datetrim for datetimes
I would like to create a Datetrim function with arguments datepart, datevalue. It should return a datetime from datevalue (which is a datetime) having every date part smaller than the datepart argument trimmed.
Eg:
(year,'20180703 11:32:45.333') should return 20180101 00:00:00.000
(year,'20180703 11:32:45.333')
20180101 00:00:00.000
(month,'20180703 11:32:45.333') should return 20180701 00:00:00.000
(month,'20180703 11:32:45.333')
20180701 00:00:00.000
I suppose the body's code would be a case on datepart with all possible dateadd-datediff combos. The question is, can I have my datepart argument be of the same (unknown to me atm) type of the datepart arguments of dateadd/datediff functions?
Or do I just make it a varchar and manually write the dateparts in the case?
cast(field as date)
I have an argument of what I am supposed to trim. If datepart=second, it should trim milliseconds. If datepart=minute, it should trim seconds and milliseonds. All the way up to datepart=year, which should trim months and below. Your solution only works for datepart=day.
– George Menoutis
Jul 3 at 7:37
That's because you asked for trimming when you actually want rounding. If you search for
T-SQL date rounding you'll find a lot of solutions, all of which end up dealing with dateadd, datediff. Other SQL dialects have such functinoality, but not T-SQL– Panagiotis Kanavos
Jul 3 at 7:37
T-SQL date rounding
dateadd, datediff
why are you asking this though? It could be a bad idea - applying functions to a field means you can't use any indexes on that field and the server will have to scan the entire table. If you wanted to find all entries in a month or year though, a FAR better solution would be to use a Calendar table with separate fields for year, month, semester, week etc. You could then join by date and select all records for a specific year, month taking advantage of indexing
– Panagiotis Kanavos
Jul 3 at 7:42
Don't think too much, it's rather a laziness thing. Instead of typing DATEADD(x_datepart,0,DATEDIFF(x_datepart,0,y_datetime)), I want to type DATETRIM(x_datepart,y_datetime). You'd still render your indexes ineffective if you used the former.
– George Menoutis
Jul 3 at 7:44
1 Answer
1
you can use dateadd() with datediff() as below
dateadd()
datediff()
declare @dt datetime = '20180703 11:32:45.333',
@flag int = 1
select dateadd(year, datediff(year, 0, @dt), 0) as [Year],
dateadd(month, datediff(month, 0, @dt), 0) as [Month],
case @flag
when 1 then dateadd(year, datediff(year, 0, @dt), 0)
when 2 then dateadd(month, datediff(month, 0, @dt), 0)
end
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Trim means remove whitespace or unwanted characters from a string. With datetime, sometimes it refers to cutting out the time part (just
cast(field as date)) You are asking about rounding though. You'll find a lot of answers if you search for the correct question– Panagiotis Kanavos
Jul 3 at 7:34