Exploring the DATE Functions in SQL

Question

Post reply

Exploring the DATE Functions in SQL

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

beervolk Grasshopper Points: 13 More actions · Answer 1

From my experience it is safe to use something like:

dateColumn >= '20200101' AND dateColumn < '20210101'

for any DATE/DATETIME of 2020 dates instead of dateColumn BETWEEN ...

plus those strings of dates without dashes 'yyyymmdd' (style 112) is preferred to me 🙂

and in dim.Time (DWH for later OLAP) I prefer to have time_key as INT in form of YEAR*10000+MONTH*100+DAY.

just sharing ... do not bit me much 😉

This reply was modified 4 years, 1 month ago by beervolk.

nitin.s.pathak 96551 SSC Journeyman Points: 76 More actions · Answer 2

Accidentally there is mistake while explaining syntax of datediff

Please refer below in bold letters

The syntax for the DATEDIFF function is as follows:

DATEADD(DATEPART, STARTDATE, ENDDATE)

The arguments accepted by the DATEADD function are as follows:

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

Just my humble opinion but, except for the fact that the newer datatypes can use fewer bytes, they pretty much suck for me because you cannot do direct data math with them, which is non-ANSI/ISO compliant. The standards do say that EndDate-StartDate = Period and StartDate+Period = EndDate, etc.

Microsoft apparently realized the boo-boo they made because of all the people wanking about not being able to easily calculate periods to the millisecond but instead of fixing the real problem, they introduced DATEDIFF_BIG.

As for the EOMONTH function, I'd have much rather had an FOMONTH function that would return a DATETIME datatype.

So far as dates prior to 01 Jan 1753, I wouldn't use SQL Server for them if I needed to work with them because of all the calendar changes made prior to that date. In other words, the dates are actually incorrect prior to that date depending on which country and religion you're working with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ckredo Grasshopper Points: 16 More actions · Answer 4

Just a quick comment re. DateAdd. I just got myself into trouble with a DateAdd function that was adding 1 year to 2/28/2019. The application was working with budgets that had a variety of end dates, with each end date always the last day in the month. We've used this application for a couple years now with no issue. Then along comes the leap year in 2020! Dateadd(year,1,'2/28/19') will return 2/28/2020. That makes sense - but it's not the end of the month! The fix is easy with an EOMONTH check, and maybe this audience won't be silly enough to forget about leap years but.....!!

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

ckredo wrote:

Just a quick comment re. DateAdd. I just got myself into trouble with a DateAdd function that was adding 1 year to 2/28/2019. The application was working with budgets that had a variety of end dates, with each end date always the last day in the month. We've used this application for a couple years now with no issue. Then along comes the leap year in 2020! Dateadd(year,1,'2/28/19') will return 2/28/2020. That makes sense - but it's not the end of the month! The fix is easy with an EOMONTH check, and maybe this audience won't be silly enough to forget about leap years but.....!!

EOMONTH does make the fix easy... provided that your column data doesn't also store times other than midnight.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)