Hi all

I try to build a variable with the first day of the year so I could calculate the YTD.

I play with DATEADD, DATEPART and DATEDIFF functions but I unable to get the date

Could someone help me?

Thanks in advanced.

Here is the helpful link.

http://sqlandme.com/2011/05/17/how-to-get-first-and-last-day-of-year-tsql/

DECLARE @TRAVELYEARS INT = 1
SELECT      DATEADD(YEAR, DATEDIFF(YEAR, 0,
            DATEADD(YEAR, @TRAVELYEARS ,GETDATE())), 0),
            'First Day of Year'
UNION ALL
SELECT      DATEADD(MILLISECOND, 3,
            DATEADD(YEAR, DATEDIFF(YEAR, 0,
            DATEADD(YEAR, @TRAVELYEARS, GETDATE())) + 1, 0)),
            'Last Day of Year'
 
SET @TRAVELYEARS = 1
SELECT      DATEADD(YEAR, DATEDIFF(YEAR, 0,
            DATEADD(YEAR, @TRAVELYEARS ,GETDATE())), 0),
            'First Day of Year'
UNION ALL
SELECT      DATEADD(MILLISECOND, 3,
            DATEADD(YEAR, DATEDIFF(YEAR, 0,
            DATEADD(YEAR, @TRAVELYEARS, GETDATE())) + 1, 0)),
            'Last Day of Year'

Result Set:

 -
2010-01-01 00:00:00.000 First Day of Next Year
2010-12-31 23:59:59.997 Last Day of Next Year
 
(2 row(s) affected)
 
                       
 -
2012-01-01 00:00:00.000 First Day of Next Year
2012-12-31 23:59:59.997 Last Day of Next Year

• Edited by Kumar muppa 12 minutes ago

You can also create a function and pass any date as parameter to return the first day of that year.

SELECT      DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0),
            'First Day of Current Year'

Thanks guys for your help but these expression doesnt work in the expression builder of SSIS

TITLE: Expression Builder
------------------------------

Expression cannot be evaluated.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2008&ProdVer=9.0.30729.1&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

The expression contains unrecognized token "YEAR". If "YEAR" is a variable, it should be expressed as "@YEAR". The specified token is not valid. If the token is intended to be a variable name, it should be prefixed with the @ symbol.

Attempt to parse the expression "DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)," failed and returned error code 0xC00470A4. The expression cannot be parsed. It might contain invalid elements or it might not be well-formed. There may also be an out-of-memory error.

 (Microsoft.DataTransformationServices.Controls)

Thanks guys for your help

this function doesnt work in the Expression builder of SSIS

Thanks guys for your help

this function doesnt work in the Expression builder of SSIS

Thanks guys for your help

this function doesnt work in the Expression builder of SSIS

Thanks guys for your help

this function doesnt work in the Expression builder of SSIS

Hi Sergio,

To get the first day of the year in SSIS variable, please refer to the following expression:
DATEADD("M",-MONTH(GETDATE())+1,DATEADD("D",-DAY(GETDATE())+1,GETDATE()))

Reference:
DATEADD (SSIS Expression)

If there are any other questions, please feel free to ask.

Thanks,
Katherine Xiong

Here is the helpful link.

http://sqlandme.com/2011/05/17/how-to-get-first-and-last-day-of-year-tsql/

DECLARE @TRAVELYEARS INT = 1
SELECT      DATEADD(YEAR, DATEDIFF(YEAR, 0,
            DATEADD(YEAR, @TRAVELYEARS ,GETDATE())), 0),
            'First Day of Year'
UNION ALL
SELECT      DATEADD(MILLISECOND, 3,
            DATEADD(YEAR, DATEDIFF(YEAR, 0,
            DATEADD(YEAR, @TRAVELYEARS, GETDATE())) + 1, 0)),
            'Last Day of Year'
 
SET @TRAVELYEARS = 1
SELECT      DATEADD(YEAR, DATEDIFF(YEAR, 0,
            DATEADD(YEAR, @TRAVELYEARS ,GETDATE())), 0),
            'First Day of Year'
UNION ALL
SELECT      DATEADD(MILLISECOND, 3,
            DATEADD(YEAR, DATEDIFF(YEAR, 0,
            DATEADD(YEAR, @TRAVELYEARS, GETDATE())) + 1, 0)),
            'Last Day of Year'

Result Set:

 -
2010-01-01 00:00:00.000 First Day of Next Year
2010-12-31 23:59:59.997 Last Day of Next Year
 
(2 row(s) affected)
 
                       
 -
2012-01-01 00:00:00.000 First Day of Next Year
2012-12-31 23:59:59.997 Last Day of Next Year

• Edited by Kumar muppa Friday, July 17, 2015 6:55 AM

Here is the helpful link.

http://sqlandme.com/2011/05/17/how-to-get-first-and-last-day-of-year-tsql/

DECLARE @TRAVELYEARS INT = 1
SELECT      DATEADD(YEAR, DATEDIFF(YEAR, 0,
            DATEADD(YEAR, @TRAVELYEARS ,GETDATE())), 0),
            'First Day of Year'
UNION ALL
SELECT      DATEADD(MILLISECOND, 3,
            DATEADD(YEAR, DATEDIFF(YEAR, 0,
            DATEADD(YEAR, @TRAVELYEARS, GETDATE())) + 1, 0)),
            'Last Day of Year'
 
SET @TRAVELYEARS = 1
SELECT      DATEADD(YEAR, DATEDIFF(YEAR, 0,
            DATEADD(YEAR, @TRAVELYEARS ,GETDATE())), 0),
            'First Day of Year'
UNION ALL
SELECT      DATEADD(MILLISECOND, 3,
            DATEADD(YEAR, DATEDIFF(YEAR, 0,
            DATEADD(YEAR, @TRAVELYEARS, GETDATE())) + 1, 0)),
            'Last Day of Year'

Result Set:

 -
2010-01-01 00:00:00.000 First Day of Next Year
2010-12-31 23:59:59.997 Last Day of Next Year
 
(2 row(s) affected)
 
                       
 -
2012-01-01 00:00:00.000 First Day of Next Year
2012-12-31 23:59:59.997 Last Day of Next Year

• Edited by Kumar muppa Friday, July 17, 2015 6:55 AM
• Proposed as answer by ryguy72 14 hours 21 minutes ago

Here is the helpful link.

http://sqlandme.com/2011/05/17/how-to-get-first-and-last-day-of-year-tsql/

DECLARE @TRAVELYEARS INT = 1
SELECT      DATEADD(YEAR, DATEDIFF(YEAR, 0,
            DATEADD(YEAR, @TRAVELYEARS ,GETDATE())), 0),
            'First Day of Year'
UNION ALL
SELECT      DATEADD(MILLISECOND, 3,
            DATEADD(YEAR, DATEDIFF(YEAR, 0,
            DATEADD(YEAR, @TRAVELYEARS, GETDATE())) + 1, 0)),
            'Last Day of Year'
 
SET @TRAVELYEARS = 1
SELECT      DATEADD(YEAR, DATEDIFF(YEAR, 0,
            DATEADD(YEAR, @TRAVELYEARS ,GETDATE())), 0),
            'First Day of Year'
UNION ALL
SELECT      DATEADD(MILLISECOND, 3,
            DATEADD(YEAR, DATEDIFF(YEAR, 0,
            DATEADD(YEAR, @TRAVELYEARS, GETDATE())) + 1, 0)),
            'Last Day of Year'

Result Set:

 -
2010-01-01 00:00:00.000 First Day of Next Year
2010-12-31 23:59:59.997 Last Day of Next Year
 
(2 row(s) affected)
 
                       
 -
2012-01-01 00:00:00.000 First Day of Next Year
2012-12-31 23:59:59.997 Last Day of Next Year

• Edited by Kumar muppa Friday, July 17, 2015 6:55 AM
• Proposed as answer by ryguy72 Saturday, July 18, 2015 4:45 PM

Why not just construct the date as a string and cast it into a date?  You can use YEAR(GETDATE()) to get the year.  Then convert it to a string by casting (DT_STR,4,1252) YEAR(GETDATE()).  Then prefix the result with "1/1/".  You should end up with "1/1/2015".  Then cast it to a date using DT_DATE or whatever your preference would be.

(DT_DATE)("1/1/" + (DT_STR,4,1252) YEAR(GETDATE()))

• Edited by J I M B O 12 hours 36 minutes ago

As an addition to Katherine Xiong's response, if you want to reset the time portion, you will also need to cast the results.

(DT_DATE)(DT_DBDATE)(DATEADD("M",-MONTH(GETDATE())+1,DATEADD("D",-DAY(GETDATE())+1,GETDATE())))

• Edited by J I M B O 12 hours 55 minutes ago Added reference to previous commentor.

Why not just construct the date as a string and cast it into a date?  You can use YEAR(GETDATE()) to get the year.  Then convert it to a string by casting (DT_STR,4,1252) YEAR(GETDATE()).  Then prefix the result with "1/1/".  You should end up with "1/1/2015".  Then cast it to a date using DT_DATE or whatever your preference would be.

(DT_DATE)("1/1/" + (DT_STR,4,1252) YEAR(GETDATE()))

• Edited by J I M B O Friday, July 17, 2015 6:31 PM

Why not just construct the date as a string and cast it into a date?  You can use YEAR(GETDATE()) to get the year.  Then convert it to a string by casting (DT_STR,4,1252) YEAR(GETDATE()).  Then prefix the result with "1/1/".  You should end up with "1/1/2015".  Then cast it to a date using DT_DATE or whatever your preference would be.

(DT_DATE)("1/1/" + (DT_STR,4,1252) YEAR(GETDATE()))

• Edited by J I M B O Friday, July 17, 2015 6:31 PM

As an addition to Katherine Xiong's response, if you want to reset the time portion, you will also need to cast the results.

(DT_DATE)(DT_DBDATE)(DATEADD("M",-MONTH(GETDATE())+1,DATEADD("D",-DAY(GETDATE())+1,GETDATE())))

• Edited by J I M B O Friday, July 17, 2015 6:12 PM Added reference to previous commentor.

As an addition to Katherine Xiong's response, if you want to reset the time portion, you will also need to cast the results.

(DT_DATE)(DT_DBDATE)(DATEADD("M",-MONTH(GETDATE())+1,DATEADD("D",-DAY(GETDATE())+1,GETDATE())))

• Edited by J I M B O Friday, July 17, 2015 6:12 PM Added reference to previous commentor.
• Marked as answer by Sergio Sanchez Sanchez 23 minutes ago

Hi all

Thanks all for your help.

I need more practice with DATEADD function.

Regards