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()

  • 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.

  • 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

  • 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()))

  • 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.

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

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy