Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL code Expand / Collapse
Author
Message
Posted Thursday, December 5, 2013 10:34 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 9:51 AM
Points: 80, Visits: 332
Hi All,

Can someone help me interpret what this code is doing.

open_date BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0) AND DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) - '00:00:01'

and is there another way to re-write it.

EO
Post #1520235
Posted Thursday, December 5, 2013 10:46 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:05 PM
Points: 3,943, Visits: 2,957
eobiki10 (12/5/2013)
Hi All,

Can someone help me interpret what this code is doing.

open_date BETWEEN DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0) AND DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) - '00:00:01'

and is there another way to re-write it.

EO

It looks to me like it's part of a WHERE clause that filters for rows where open_date is in the current month including time of day.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1520239
Posted Thursday, December 5, 2013 10:49 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:20 PM
Points: 3,332, Visits: 7,187
DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0)

Is getting the first day of previous month.

DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)

Is getting the first day of current month.

 - '00:00:01'

Is substracting one second to get only values from previous month because BETWEEN limits are inclusive.

There are ways to rewrite the code, but this might be the best in terms of performance and flexibility. It's basically adding months to a base date (zero or 1900-01-01) to eliminate days and time. You could use the same functionality for other datetime parts.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1520241
Posted Thursday, December 5, 2013 10:52 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, July 18, 2014 2:05 PM
Points: 3,943, Visits: 2,957
Luis Cazares (12/5/2013)
DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0)

Is getting the first day of previous month.

DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)

Is getting the first day of current month.

 - '00:00:01'

Is substracting one second to get only values from previous month because BETWEEN limits are inclusive.

There are ways to rewrite the code, but this might be the best in terms of performance and flexibility. It's basically adding months to a base date (zero or 1900-01-01) to eliminate days and time. You could use the same functionality for other datetime parts.

DOH! I typed current month. Luis is right - it's previous month. Thanks for catching it, Luis.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1520244
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse