Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

dateadd & datediff

Posted on 2014-10-01
7
Medium Priority
?
932 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 15

Accepted Solution

by:
Vikas Garg earned 2000 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 15

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 49

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 49

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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

715 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