Post reply

Convert DateTime to String in SSIS

  • July 9, 2015 at 3:47 am

    #302222

    Hi,

    I just want to ask for help on how to convert Datetime to String in SSIS.

    >First Day (Previous Month)

    (DT_DATE)((DT_WSTR, 4)YEAR(DATEADD("mm", -1, GETDATE())) + "-" +RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("mm", -1, GETDATE())),2)+"-01")

    >Last Day (Previous Month)

    DATEADD("dd", -1, (DT_DATE)((DT_WSTR, 4)YEAR(GETDATE()) + "-" +RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()),2)+"-01"))

    Hoping for your replies.

    Thank you very much.

    Gilbert

  • July 9, 2015 at 4:25 am

    #1811230

    This converts a datetime to a string.

    (DT_WSTR, 30) GETDATE()

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • July 9, 2015 at 4:36 am

    #1811231

    Hi,

    You mean like this?

    (DT_WSTR, 30) DATEADD("dd", -1, (DT_DATE)((DT_WSTR, 4)YEAR(GETDATE()) + "-" +RIGHT("0" + (DT_WSTR,2)MONTH(GETDATE()),2)+"-01"))

    I'm sorry. Just a newbie in SSIS.

    Thank you.

  • July 9, 2015 at 5:11 am

    #1811237

    If you will take a step back and tell me

    a) What you are trying to achieve and

    b) What format you want the result in

    I might be able to help you more.

    Your original post merely asked for help converting a datetime to a string and my response demonstrated that.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • July 9, 2015 at 5:15 am

    #1811238

    Hi,

    Thank you.

    What I want to achieve are:

    a) get the first day date of the previous month based on current date

    b) get the last day date of the previous month based on current date

    c) format will be in mm/dd/yyyy

    d) should be converted to string

    Thank you again 😀

  • July 9, 2015 at 5:20 am

    #1811239

    endayagilbert (7/9/2015)

    Hi,

    Thank you.

    What I want to achieve are:

    a) get the first day date of the previous month based on current date

    b) get the last day date of the previous month based on current date

    c) format will be in mm/dd/yyyy

    d) should be converted to string

    Thank you again 😀

    A single string? Like this?

    06/01/2015 06/30/2015

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • July 9, 2015 at 5:35 am

    #1811250

    First day of previous month:

    right("0" + (DT_STR, 2, 1252) MONTH( dateadd("mm",-1,getdate())),2) + "/01/"+ (DT_STR, 4,1252) year(dateadd("mm",-1,getdate()))

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • July 9, 2015 at 5:51 am

    #1811254

    Last day of previous month is this, but not in the format you want.

    (DT_WSTR, 40) dateadd("d",-1,Dateadd("m",DATEDIFF( "m", (DT_DBTIMESTAMP) "1900-01-01", getdate() ), (DT_DBTIMESTAMP) "1900-01-01"))

    I'll leave it to you to use DAY(), MONTH() and YEAR() on the above to produce the format you are after.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 8 posts - 1 through 7 (of 7 total)

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