storing datetime2 in ssis variable












0














I'm using SQL Server 2012 Enterprise. I have a requirement where I've to store data with DATETIME2 datatype in SSIS Variable. Unfortunately, SSIS variables don't have that data type.



If I'm storing it into datetime data type, I'm losing information. Can anyone help in giving workaround?



PS: My source system is SQL Server 2012 as well and I'm reading the data from column with datetime2 datatype.






sql-server datetime ssis







share|improve this question






















  • I dont know about SSIS variables... can you try that, store the datetime2 as varchar or string in SSIS.? If did, then you can convert varchar to datetime2 in SQL Server.
    – IdontKnowEnglish
    Dec 28 '18 at 8:08










  • what are you losing exactly? It is DateTime format from (.Net Framework)
    – Simonare
    Dec 28 '18 at 8:14






  • 1




    Looks like DT_DBTIMESTAMP2 should work: docs.microsoft.com/en-us/sql/integration-services/data-flow/…
    – Peter B
    Dec 28 '18 at 8:19










  • Depends what you mean by "store". When in a dataflow task, then @PeterB is correct, you want DT_DBTIMESTAMP2. If you're declaring a variable, however, then you only have the option of datetime. The documentation doesn't state what value types are available, however, the DateTime datatype is equivilent to a datetime2(0).
    – Larnu
    Dec 28 '18 at 9:05








  • 1




    If you need a higher scale you are (unfortunately) forced to use the string datatype, or convert your datetime is an numerical and work with that. For example, maybe you'll store 2018-12-28 09:06:34.123456 as 20181229090634.123456 or maybe 599303194123456000 (number of nanoseconds between 2000-01-01 and the date). It is a known "shortfall" of SSIS when working with its variables.
    – Larnu
    Dec 28 '18 at 9:05


















0














I'm using SQL Server 2012 Enterprise. I have a requirement where I've to store data with DATETIME2 datatype in SSIS Variable. Unfortunately, SSIS variables don't have that data type.



If I'm storing it into datetime data type, I'm losing information. Can anyone help in giving workaround?



PS: My source system is SQL Server 2012 as well and I'm reading the data from column with datetime2 datatype.






sql-server datetime ssis







share|improve this question






















  • I dont know about SSIS variables... can you try that, store the datetime2 as varchar or string in SSIS.? If did, then you can convert varchar to datetime2 in SQL Server.
    – IdontKnowEnglish
    Dec 28 '18 at 8:08










  • what are you losing exactly? It is DateTime format from (.Net Framework)
    – Simonare
    Dec 28 '18 at 8:14






  • 1




    Looks like DT_DBTIMESTAMP2 should work: docs.microsoft.com/en-us/sql/integration-services/data-flow/…
    – Peter B
    Dec 28 '18 at 8:19










  • Depends what you mean by "store". When in a dataflow task, then @PeterB is correct, you want DT_DBTIMESTAMP2. If you're declaring a variable, however, then you only have the option of datetime. The documentation doesn't state what value types are available, however, the DateTime datatype is equivilent to a datetime2(0).
    – Larnu
    Dec 28 '18 at 9:05








  • 1




    If you need a higher scale you are (unfortunately) forced to use the string datatype, or convert your datetime is an numerical and work with that. For example, maybe you'll store 2018-12-28 09:06:34.123456 as 20181229090634.123456 or maybe 599303194123456000 (number of nanoseconds between 2000-01-01 and the date). It is a known "shortfall" of SSIS when working with its variables.
    – Larnu
    Dec 28 '18 at 9:05
















0












0








0







I'm using SQL Server 2012 Enterprise. I have a requirement where I've to store data with DATETIME2 datatype in SSIS Variable. Unfortunately, SSIS variables don't have that data type.



If I'm storing it into datetime data type, I'm losing information. Can anyone help in giving workaround?



PS: My source system is SQL Server 2012 as well and I'm reading the data from column with datetime2 datatype.






sql-server datetime ssis







share|improve this question













I'm using SQL Server 2012 Enterprise. I have a requirement where I've to store data with DATETIME2 datatype in SSIS Variable. Unfortunately, SSIS variables don't have that data type.



If I'm storing it into datetime data type, I'm losing information. Can anyone help in giving workaround?



PS: My source system is SQL Server 2012 as well and I'm reading the data from column with datetime2 datatype.






sql-server datetime ssis




sql-server datetime ssis






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Dec 28 '18 at 7:57









Prabhat GPrabhat G

2,47611324




2,47611324












  • I dont know about SSIS variables... can you try that, store the datetime2 as varchar or string in SSIS.? If did, then you can convert varchar to datetime2 in SQL Server.
    – IdontKnowEnglish
    Dec 28 '18 at 8:08










  • what are you losing exactly? It is DateTime format from (.Net Framework)
    – Simonare
    Dec 28 '18 at 8:14






  • 1




    Looks like DT_DBTIMESTAMP2 should work: docs.microsoft.com/en-us/sql/integration-services/data-flow/…
    – Peter B
    Dec 28 '18 at 8:19










  • Depends what you mean by "store". When in a dataflow task, then @PeterB is correct, you want DT_DBTIMESTAMP2. If you're declaring a variable, however, then you only have the option of datetime. The documentation doesn't state what value types are available, however, the DateTime datatype is equivilent to a datetime2(0).
    – Larnu
    Dec 28 '18 at 9:05








  • 1




    If you need a higher scale you are (unfortunately) forced to use the string datatype, or convert your datetime is an numerical and work with that. For example, maybe you'll store 2018-12-28 09:06:34.123456 as 20181229090634.123456 or maybe 599303194123456000 (number of nanoseconds between 2000-01-01 and the date). It is a known "shortfall" of SSIS when working with its variables.
    – Larnu
    Dec 28 '18 at 9:05




















  • I dont know about SSIS variables... can you try that, store the datetime2 as varchar or string in SSIS.? If did, then you can convert varchar to datetime2 in SQL Server.
    – IdontKnowEnglish
    Dec 28 '18 at 8:08










  • what are you losing exactly? It is DateTime format from (.Net Framework)
    – Simonare
    Dec 28 '18 at 8:14






  • 1




    Looks like DT_DBTIMESTAMP2 should work: docs.microsoft.com/en-us/sql/integration-services/data-flow/…
    – Peter B
    Dec 28 '18 at 8:19










  • Depends what you mean by "store". When in a dataflow task, then @PeterB is correct, you want DT_DBTIMESTAMP2. If you're declaring a variable, however, then you only have the option of datetime. The documentation doesn't state what value types are available, however, the DateTime datatype is equivilent to a datetime2(0).
    – Larnu
    Dec 28 '18 at 9:05








  • 1




    If you need a higher scale you are (unfortunately) forced to use the string datatype, or convert your datetime is an numerical and work with that. For example, maybe you'll store 2018-12-28 09:06:34.123456 as 20181229090634.123456 or maybe 599303194123456000 (number of nanoseconds between 2000-01-01 and the date). It is a known "shortfall" of SSIS when working with its variables.
    – Larnu
    Dec 28 '18 at 9:05


















I dont know about SSIS variables... can you try that, store the datetime2 as varchar or string in SSIS.? If did, then you can convert varchar to datetime2 in SQL Server.
– IdontKnowEnglish
Dec 28 '18 at 8:08




I dont know about SSIS variables... can you try that, store the datetime2 as varchar or string in SSIS.? If did, then you can convert varchar to datetime2 in SQL Server.
– IdontKnowEnglish
Dec 28 '18 at 8:08












what are you losing exactly? It is DateTime format from (.Net Framework)
– Simonare
Dec 28 '18 at 8:14




what are you losing exactly? It is DateTime format from (.Net Framework)
– Simonare
Dec 28 '18 at 8:14




1




1




Looks like DT_DBTIMESTAMP2 should work: docs.microsoft.com/en-us/sql/integration-services/data-flow/…
– Peter B
Dec 28 '18 at 8:19




Looks like DT_DBTIMESTAMP2 should work: docs.microsoft.com/en-us/sql/integration-services/data-flow/…
– Peter B
Dec 28 '18 at 8:19












Depends what you mean by "store". When in a dataflow task, then @PeterB is correct, you want DT_DBTIMESTAMP2. If you're declaring a variable, however, then you only have the option of datetime. The documentation doesn't state what value types are available, however, the DateTime datatype is equivilent to a datetime2(0).
– Larnu
Dec 28 '18 at 9:05






Depends what you mean by "store". When in a dataflow task, then @PeterB is correct, you want DT_DBTIMESTAMP2. If you're declaring a variable, however, then you only have the option of datetime. The documentation doesn't state what value types are available, however, the DateTime datatype is equivilent to a datetime2(0).
– Larnu
Dec 28 '18 at 9:05






1




1




If you need a higher scale you are (unfortunately) forced to use the string datatype, or convert your datetime is an numerical and work with that. For example, maybe you'll store 2018-12-28 09:06:34.123456 as 20181229090634.123456 or maybe 599303194123456000 (number of nanoseconds between 2000-01-01 and the date). It is a known "shortfall" of SSIS when working with its variables.
– Larnu
Dec 28 '18 at 9:05






If you need a higher scale you are (unfortunately) forced to use the string datatype, or convert your datetime is an numerical and work with that. For example, maybe you'll store 2018-12-28 09:06:34.123456 as 20181229090634.123456 or maybe 599303194123456000 (number of nanoseconds between 2000-01-01 and the date). It is a known "shortfall" of SSIS when working with its variables.
– Larnu
Dec 28 '18 at 9:05














1 Answer
1






active

oldest

votes


















1














SSIS, at least currently, has a "known" shortfall in that the variable value type, DateTime only has a precision to the second; effectively the same as a datetime2(0). If you therefore need to store anything more accurate that a second, such as if you are using datetime and the 1/300 of a second is important or if you are using datetime2 with a precision of 1 or more, the value type DateTime, will not serve your goal.



A couple of different options are therefore to store the value of as a String or numerical value. This does, however, come with it's own problems; most and foremost that neither of these datatypes are date and time datatypes.



It therefore depends what your goal is. I would most likely make use of a String datatype and ensure it has the ISO format ('yyyy-MM-ddThh:mm:ss.nnnnnnn'). If you're then using something like a T-SQL Task you can pass your variable as normal to the task and the data engine will interpret the literal string as a datetime2(7) (or whichever literal precision you used).






share|improve this answer





















    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53955344%2fstoring-datetime2-in-ssis-variable%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    1 Answer
    1






    active

    oldest

    votes








    1 Answer
    1






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    1














    SSIS, at least currently, has a "known" shortfall in that the variable value type, DateTime only has a precision to the second; effectively the same as a datetime2(0). If you therefore need to store anything more accurate that a second, such as if you are using datetime and the 1/300 of a second is important or if you are using datetime2 with a precision of 1 or more, the value type DateTime, will not serve your goal.



    A couple of different options are therefore to store the value of as a String or numerical value. This does, however, come with it's own problems; most and foremost that neither of these datatypes are date and time datatypes.



    It therefore depends what your goal is. I would most likely make use of a String datatype and ensure it has the ISO format ('yyyy-MM-ddThh:mm:ss.nnnnnnn'). If you're then using something like a T-SQL Task you can pass your variable as normal to the task and the data engine will interpret the literal string as a datetime2(7) (or whichever literal precision you used).






    share|improve this answer


























      1














      SSIS, at least currently, has a "known" shortfall in that the variable value type, DateTime only has a precision to the second; effectively the same as a datetime2(0). If you therefore need to store anything more accurate that a second, such as if you are using datetime and the 1/300 of a second is important or if you are using datetime2 with a precision of 1 or more, the value type DateTime, will not serve your goal.



      A couple of different options are therefore to store the value of as a String or numerical value. This does, however, come with it's own problems; most and foremost that neither of these datatypes are date and time datatypes.



      It therefore depends what your goal is. I would most likely make use of a String datatype and ensure it has the ISO format ('yyyy-MM-ddThh:mm:ss.nnnnnnn'). If you're then using something like a T-SQL Task you can pass your variable as normal to the task and the data engine will interpret the literal string as a datetime2(7) (or whichever literal precision you used).






      share|improve this answer
























        1












        1








        1






        SSIS, at least currently, has a "known" shortfall in that the variable value type, DateTime only has a precision to the second; effectively the same as a datetime2(0). If you therefore need to store anything more accurate that a second, such as if you are using datetime and the 1/300 of a second is important or if you are using datetime2 with a precision of 1 or more, the value type DateTime, will not serve your goal.



        A couple of different options are therefore to store the value of as a String or numerical value. This does, however, come with it's own problems; most and foremost that neither of these datatypes are date and time datatypes.



        It therefore depends what your goal is. I would most likely make use of a String datatype and ensure it has the ISO format ('yyyy-MM-ddThh:mm:ss.nnnnnnn'). If you're then using something like a T-SQL Task you can pass your variable as normal to the task and the data engine will interpret the literal string as a datetime2(7) (or whichever literal precision you used).






        share|improve this answer












        SSIS, at least currently, has a "known" shortfall in that the variable value type, DateTime only has a precision to the second; effectively the same as a datetime2(0). If you therefore need to store anything more accurate that a second, such as if you are using datetime and the 1/300 of a second is important or if you are using datetime2 with a precision of 1 or more, the value type DateTime, will not serve your goal.



        A couple of different options are therefore to store the value of as a String or numerical value. This does, however, come with it's own problems; most and foremost that neither of these datatypes are date and time datatypes.



        It therefore depends what your goal is. I would most likely make use of a String datatype and ensure it has the ISO format ('yyyy-MM-ddThh:mm:ss.nnnnnnn'). If you're then using something like a T-SQL Task you can pass your variable as normal to the task and the data engine will interpret the literal string as a datetime2(7) (or whichever literal precision you used).







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Dec 28 '18 at 9:36









        LarnuLarnu

        16.1k41630




        16.1k41630






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid …



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.





            Some of your past answers have not been well-received, and you're in danger of being blocked from answering.


            Please pay close attention to the following guidance:


            • Please be sure to answer the question. Provide details and share your research!

            But avoid …



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53955344%2fstoring-datetime2-in-ssis-variable%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            -

            Popular posts from this blog

            Monofisismo

            Image
            Monofisismo (do grego: monos - "único, singular" e physis - "natureza") é o ponto de vista cristológico que defende que, depois da união do divino e do humano na encarnação histórica, Jesus Cristo, como encarnação do Filho ou Verbo ( Logos ) de Deus, teria apenas uma única "natureza", a divina, e não uma síntese de ambas. O monofisismo é contraposto pelo diofisismo (ou "diafisismo"), que defende que Jesus preservou em si as duas naturezas. Historicamente, o monofisismo se refere primordialmente à posição dos que (especialmente no Egito e, em menor grau, na Síria) rejeitaram as decisões do Concílio de Calcedônia em 451 (o quarto concílio ecumênico). Os membros mais moderados entre eles, porém, defendem a teologia "miafisita", que se tornou a oficial para as Igrejas Ortodoxas Orientais. Muitos ortodoxos orientais, porém, rejeitam essa classificação, mesmo como um termo genérico, mas ele é amplamente utilizado na literatura históri...
            Read more

            Angular Downloading a file using contenturl with Basic Authentication

            Image
            0 I have a contentUrl when user clicks on that,file should be downloaded. If I try to browse the contentUrl in the browser it asks for Userid and password.I believe this is Basic Authentication.I dont want user to enter UserId and Password so I am passing it Authorization(Please see below code). Url looks some thing like this: http://XXX.XXX.XX.XXX:8080/flowable-rest/service/runtime/tasks/90875/attachments/90555/content let username : string = 'admin'; let pwd : string = 'test'; let headers = new Headers(); headers.set('Accept', 'text/json'); headers.append('Content-Type', 'application/json'); headers.set('Access-Control-Allow-Origin', '*'); headers.set('Access-Control-Allow-Methods','POST, GET, OPTIONS, PUT, DELETE'); head...
            Read more

            Olmecas

            Image
            Monumento 1, uma das quatro cabeças colossais encontradas em La Venta, com altura aproximada de 3 metros Olmecas é a designação do povo e da civilização que estiveram na origem da antiga cultura pré-colombiana da Mesoamérica e que se desenvolveram nas regiões tropicais do centro-sul do atual México durante o pré-clássico, próximo de onde hoje estão localizados os estados mexicanos de Veracruz e Tabasco, no Istmo de Tehuantepec, numa zona designada área nuclear olmeca. A cultura olmeca floresceu nesta região aproximadamente entre 1500 e 400 a.C., [ 1 ] e crê-se que tenha sido a civilização-mãe de todas as civilizações mesoamericanas que se desenvolveram posteriormente. [ 2 ] No entanto, desconhece-se a sua exacta filiação étnica, ainda que existam numerosas hipóteses colocadas para tentar resolver esta questão. O etnónimo olmeca foi cunhado pelos arqueólogos do século XX, e não devem confundir-se com os muito posteriores olmecas-xicalancas que ocuparam vários locais do México...
            Read more