Post reply

how to eliminate time part and format date in different format than in sql server

  • October 20, 2008 at 6:06 pm

    #124419

    Hello All,

    in my package process i am reading data from source table and then writing to CSV file

    in my source sql server table, DueDate column has data format as M/DD/YYYY HH:MM:SS AM (Ex 1/30/2008 12:00:00 AM)

    how can i convert the data to "YYYY-DD-MM" format (ex: 2008-01-30 )

    i trried to use with derived columns but i am getting many errors

    can any one please let me know

    if in sql server table it is YYYY-MM-DD format then we can get it by using substring but in sql server data format is different.

    Thanks in advance

    asini

  • October 20, 2008 at 8:42 pm

    #887403

    you can use the built in functions to get you pretty close:

    select getdate(),convert(varchar,getdate(),112)

    2008-10-20 22:34:41.680

    gets formatted to

    20081020

    you could then use some string functions to get the dasheted in there

    select substring(convert(varchar,getdate(),112),1,4)

    + '-'

    + substring(convert(varchar,getdate(),112),5,2)

    + '-'

    + substring(convert(varchar,getdate(),112),7,2)

    2008-10-20

    see bol for more formats

    http://msdn.microsoft.com/en-us/library/ms187928.aspx

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 21, 2008 at 8:57 am

    #887647

    Hello Lowell,

    thanks for you reply,

    here i have a small question where can i use select cast functions in SSIS package.

    in derived column expressions if i use the convery function it is error

    so is there any alternative way to use this sql functions in SSIS package

    Best Regards

    asin

  • October 21, 2008 at 10:16 am

    #887708

    Hello Lowel,

    its got worked with below function

    (DT_STR,4,1252)YEAR([Due Date]) + "-" + (LEN((DT_STR,2,1252)MONTH([Due Date])) == 1 ? "0" + (DT_STR,2,1252)MONTH([Due Date]) : (DT_STR,2,1252)MONTH([Due Date])) + "-" + (LEN((DT_STR,2,1252)DAY([Due Date])) == 1 ? "0" + (DT_STR,2,1252)DAY([Due Date]) : (DT_STR,2,1252)DAY([Due Date]))

    Thanks & Regards

    asin

  • October 21, 2008 at 11:41 am

    #887755

    Hi,

    If you are querying from SQL Table wont you do like Datepart(mm, ) instead of using cast, convert ?

    Rajesh

Viewing 5 posts - 1 through 4 (of 4 total)

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