SSIS Expression

SSIS and Import/Export
1

Hi Guys,/

Is anyone can help me convert this SQL to SSIS Expression?

SELECT DATEADD(DAY,DATEDIFF(DAY,0,CONVERT(DATE,DATEADD(WK,DATEDIFF(WK,0,GETDATE()),0) - 8)), '00:00:00')

Thanks in Advance.

2 

convert(datetime, cast(getdate() as date), 126)

select distinct convert(datetime, cast(getdate() as date), column_id), 
          column_id
  from sys.all_columns
  where column_id in (13,113,114, 20, 120, 21, 25, 121, 126, 127, 131)
  order by column_id
3

try this. Set the variable as DateTime and put this in the Expression

(DT_DATE)( (DT_STR, 2, 1252) Month(GETDATE()) + (DT_STR, 1, 1252)"/" + (DT_STR, 2, 1252) Day(GetDate()) + (DT_STR, 1, 1252)"/" + (DT_STR, 4, 1252) year(GetDate()) + (DT_STR, 9, 1252)" 00:00:00")

4

Hi Mike,
Thanks for your reply. Just making sure I understand correctly.

  1. Create a new variable as DateTime and Put the above expression there.
  2. How and where I use a new variable to accomplish what I need?

Please advice.

5

I created a DateTime package variable and pasted that in the expression of the variable

6

The above expression will just give you today's date. I am looking to convert this SQL into SSIS Expression.

SELECT DATEADD(DAY,DATEDIFF(DAY,0,CONVERT(DATE,DATEADD(WK,DATEDIFF(WK,0,GETDATE()),0) - 8)), '00:00:00')

7

DATEADD("day", -8, DATEADD( "week", ( DATEDIFF( "week",( (DT_DATE) ("1/1/1900 00:00:00")) ,( (DT_DATE)( (DT_STR, 2, 1252) Month(GETDATE()) + (DT_STR, 1, 1252)"/" + (DT_STR, 2, 1252) Day(GetDate()) + (DT_STR, 1, 1252)"/" + (DT_STR, 4, 1252) year(GetDate()) + (DT_STR, 9, 1252)" 00:00:00")))), (DT_DATE) ("1/1/1900 00:00:00")) )

8

is your first day of the week Sunday or Monday?

9

Hi Mike, Thank you so much for your effort and sorry for late reply. Here I got the solution.

DATEADD("DD", -8, DATEADD("WK",DATEDIFF( "WK", @[User::starting], GETDATE()), @[User::starting] ) )

@starting variable has "01/01/1900"