CODEWITHSUNDEEP
sqlsql-server
peter.petrov
peter.petrov

Reputation: 39457

datediff weird stuff - SQL Server

TEST:

    select datediff(week, '20190104 23:01:11.821', '20190105 23:01:11.821')
    select datediff(week, '20190104 23:01:11.821', '20190106 23:01:11.821')
    select datediff(week, '20190104 23:01:11.821', '20190107 23:01:11.821')
    select datediff(week, '20190104 23:01:11.821', '20190108 23:01:11.821')
    select datediff(week, '20190104 23:01:11.821', '20190109 23:01:11.821')
    select datediff(week, '20190104 23:01:11.821', '20190113 23:01:11.821')

This returns 

    0
    1
    1
    1
    2

I see common sense only in the 1st return value. What is going on? E.g. how come the last one returns 2 weeks?! How come the others return what they return?

Upvotes: 0

Views: 83

Answers (3)

arce.est
arce.est

Reputation: 379

Did you identify what is the first day of the week?

you can consult it in the following way indicating a date and you will know in what position of the week it is.

SELECT DATEPART(WEEKDAY, GETDATE())

for example and according to your dates, if the day of the week was Sunday, "I would be right".

Upvotes: -2

Gordon Linoff
Gordon Linoff

Reputation: 1269673

The datediff() function returns number of date part boundaries between two dates.

So, datediff(year, '2018-12-31', '2020-01-01') will return "2", because there are two year boundaries.

Week boundaries occur on Saturday night/Sunday morning. There are two Saturday nights, so you get two weeks.

If you want a closer approximation of the number of weeks, use day and divide by 7.

Upvotes: 3

HoneyBadger
HoneyBadger

Reputation: 15140

Datediff counts how often a border is crossed.

SELECT DATEPART(WEEK, '20190104 23:01:11.821')
,       DATEPART(WEEK, '20190106 23:01:11.821')

Returns

1   2

So one week border was crossed, so datediff returns 1.

Upvotes: 3

Related Questions