Dateformat help in SSIS Variable

NickK 176 Reputation points
2021-09-29T21:29:44.68+00:00

Hi Experts, can someone provide me a tip/Script for below?
I am declaring a variable and setting a value in ssis

when i set varibale like below: i get: 2021-09-01
DT_STR, 4, 1252) DATEPART("yy" , GETDATE()) + "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2) + "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , DATEADD("Day",-28,GETDATE())), 2)

when i set varibale like below: i get: 2021-08-01
(DT_STR, 4, 1252) DATEPART("yy" , GETDATE())+ "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , DATEADD("mm",-1,GETDATE())), 2) + "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , DATEADD("Day",-28,GETDATE())), 2)
2021-09-01

but when I try to get hour-minute , I am not getting
(DT_STR, 4, 1252) DATEPART("yy" , GETDATE())+ "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , DATEADD("mm",-1,GETDATE())), 2) + "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("dd" , DATEADD("Day",-28,GETDATE())), 2) (DT_STR, 4, 1252) + ' '+ DATEPART("Hour" , GETDATE())+ "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("Minute" , DATEADD("mm",-1,GETDATE())), 2) + "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("Second" , DATEADD("Day",-28,GETDATE())), 2)

Kindly please help to get the output in

2021-09-21 17:31:09.473 fromat

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,508 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,596 questions
{count} votes

Accepted answer
  1. ZoeHui-MSFT 34,996 Reputation points
    2021-09-30T01:27:33.757+00:00

    Hi @NickK ,

    Do you mean that you want to show timestamp in the value, if yes you may refer below.

     (DT_STR, 4, 1252) DATEPART("yy" , GETDATE())  
     + "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("mm" , GETDATE()), 2) + "-" + RIGHT("0" +(DT_STR, 2, 1252)DATEPART("dd" , DATEADD("Day",-9,GETDATE())), 2)+" " +RIGHT("0" + (DT_STR, 2, 1252)DATEPART("hh", GETDATE()), 2) +":"+RIGHT("0" + (DT_STR, 2, 1252)DATEPART("mi",  GETDATE()), 2) +":"+RIGHT("0" + (DT_STR, 2, 1252)DATEPART("ss",  GETDATE()), 2)+":"+RIGHT("0" + (DT_STR, 4, 1252)DATEPART("ms",  GETDATE()), 4)  
    

    The result will be like:

    136493-screenshot-2021-09-30-102425.jpg

    If I misunderstand your issue, please incorrect me.

    Regards,

    Zoe


    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues October

    0 comments No comments

0 additional answers

Sort by: Most helpful