Between current year and current date syntax

Transact-SQL
1

I need a date function to get records between the 1st day of current year and current date (getdate())

Thanks

2

Use this in your WHERE clause or JOIN condition

yourDateColumn >= DATEFROMPARTS(YEAR(GETDATE()),1,1)
AND yourDateColumn <= GETDATE();

If you are on a version earlier than SQL 2012, use this instead

yourDateColumn >= DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)
AND yourDateColumn <= GETDATE();
3

Interesting. Is

DATEFROMPARTS(YEAR(GETDATE()),1,1)

now deemed more efficient than the old:

DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0)

?

What's your definition of "current date"? Including [part of] today? up to THIS millisecond? and not including 1 ms AFTER the current time? (i.e. a record that was in the process of being generated at the time the query launched).

GetDate() is NOW to the current millisecond - it is not "midnight last night" nor "midnight tonight". If that's what you want that's fine, of course :), but I just wanted to check.

4

I didn't mean to imply that DATEFROMPARTS is better. I don't know one way or the other. The only reason I posted both was that I wrote the DATEFROMPARTS version first and then only remembered that that was a SQL 2012 feature.

If I had to guess, I would guess there would not be any noticeable difference between the two. The DATEFROMPARTS is a little easier on the eye, so that might be one reason to use it.

5

:smile: that's good enough for me :smile:

I've no argument with that ... there was debate here, years and year ago, about the best way to truncate TIME from a day, or to round a date to "1st-of-the-month" and so on, and the hard-to-read DATEADD...DATEDIFF won every time because it used solely integer maths,

I wonder if nowadays truncating the time is better done with CAST the DATETIME value to DATE?

But that doesn't help with 1st-of-the-month rounding and so on.