VBdotnet2005
asked on
dateadd & datediff
I am trying to learn dateadd and datediff. To get the first day of the month, we use below.
SELECT DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0) AS [Date Part Only]
Why this alone select DATEDIFF(DD, 0, GETDATE()) returns 41911? And what this is , 0) at the end means?
DATEADD(datepart, interval, date)
DATEDIFF(datepart, start_date, end_date)
SELECT DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0) AS [Date Part Only]
Why this alone select DATEDIFF(DD, 0, GETDATE()) returns 41911? And what this is , 0) at the end means?
DATEADD(datepart, interval, date)
DATEDIFF(datepart, start_date, end_date)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes.
Yes,
As you saw that 0 = '1900-01-01'
so
1 means '1900-01-02' and so on ...
As you saw that 0 = '1900-01-01'
so
1 means '1900-01-02' and so on ...
ASKER
thank you
There are some people who suggest not using zero, but to specify the date in those functions so the function calls are more "self-documenting"
The base date of the SQL Server calendar is (as already noted above) Jan 1st 1900 or in the safest string format: '19000101'
Replace the zeros in the function calls with that date literal:
SELECT DATEADD(DD, DATEDIFF(DD, '19000101', GETDATE()) , '19000101') AS [Date Part Only]
^a ^b
^a get the number of whole days from 1900-01-01 until today (and hence ignoring time of day)
^b add back the whole number of days from 1900-01-01 and we arrive at today at precisely 00:00:00+00000
But you could have done this:
CAST(getdate() AS DATE)
CAST( CAST(getdate() AS DATE) AS DATETIME) -- if you specifically need a datetime
The base date of the SQL Server calendar is (as already noted above) Jan 1st 1900 or in the safest string format: '19000101'
Replace the zeros in the function calls with that date literal:
SELECT DATEADD(DD, DATEDIFF(DD, '19000101', GETDATE()) , '19000101') AS [Date Part Only]
^a ^b
^a get the number of whole days from 1900-01-01 until today (and hence ignoring time of day)
^b add back the whole number of days from 1900-01-01 and we arrive at today at precisely 00:00:00+00000
But you could have done this:
CAST(getdate() AS DATE)
CAST( CAST(getdate() AS DATE) AS DATETIME) -- if you specifically need a datetime
for first on the current month:
then deduct (that number - 1)
to arrive at the 1st of the current month
e.g. if "today" was 22nd September
22 if the day number
22-1 = 21
deduct 21 days from 22nd September and we arrive at 1st of September
SELECT
DATEADD(dd, -(DAY(GETDATE()) - 1), DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AS 'm1 First Day of Current Month'
, DATEADD(dd, -(DAY(GETDATE()) - 1), CAST(GETDATE() AS date)) AS 'm2 First Day of Current Month'
, CAST(DATEADD(dd, -(DAY(GETDATE()) - 1), CAST(GETDATE() AS date)) AS datetime) AS 'm3 First Day of Current Month'
You need to find the "day" portion of todaythen deduct (that number - 1)
to arrive at the 1st of the current month
e.g. if "today" was 22nd September
22 if the day number
22-1 = 21
deduct 21 days from 22nd September and we arrive at 1st of September
ASKER
select DATEADD(DD, 41911, 0)
returns 2014-10-01 00:00:00.000
select DATEADD(DD, 41911, 1)
returns 2014-10-02 00:00:00.000
can I increment the last parameter? like , 1 or 2 or 3, etc) or it does not work that way?