Solved

dateadd & datediff

Posted on 2014-10-01
7
755 Views
Last Modified: 2014-10-01
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)
0
Comment
Question by:VBdotnet2005
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 14

Accepted Solution

by:
Vikas Garg earned 500 total points
ID: 40356307
In  SQL

'1900-01-01 00:00:00.000' is the default date so if you take any int value as date then if it is 0 it is consider as '1900-01-01 00:00:00.000'

So Datediff will return the difference between two dates which means that
DATEDIFF(DD, 0, GETDATE()) = DATEDIFF(DD, '1900-01-01', GETDATE()) hence gives value 41912

Same way dateadd will add defined unit (day, month) to the given date

So DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0)
= DATEADD(DD, 41912, '1900-01-01')

Hope this would clear your doubts
0
 

Author Comment

by:VBdotnet2005
ID: 40356313
I get it now. 0 = 1900-01-01 00:00:00.000

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?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40356321
Yes.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 14

Expert Comment

by:Vikas Garg
ID: 40356324
Yes,
As you saw that 0 = '1900-01-01'
so
1 means '1900-01-02' and so on ...
0
 

Author Closing Comment

by:VBdotnet2005
ID: 40356347
thank you
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40356350
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
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40356362
for first on the current month:
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'

Open in new window

You need to find the "day" portion of today
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
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now