How to get the date part from a date with timestamp column in SQL Server?

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;







up vote
2
down vote

favorite












I want to get the date in DDMMYYY format from a field that stores date in YYYY-MM-DD Timestamp format. (For example, '2018-11-09' should display '09Nov2018').



I know that one way is to use the datepart function (https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017) which can extract the year, month and day individually and then I can concat them.



Edit: I don't want to use getdate(). There is a column which has YYYY-MM-DD Timestamp format and from that column I am extracting 'DDMM2018'. I am using:



concat
(
CONVERT(varchar, DATEPART(dd,columndate)),
CONVERT(varchar, LEFT(DATENAME(month, DATEPART(month,columndate)),3)) ,
CONVERT(varchar, DATEPART(year, columndate))
) AS trial


This gives me '9Nov2018' and not '09Nov2018'. I am planning to convert this back to `datetype' again as that is how I want the result.



Is there any other way to achieve this?



Any ideas/suggestions much appreciated.










share|improve this question



















  • 1




    If the data type is date, then it doesn't store it in any format, that is just how SSMS chooses to show it to you.
    – Aaron Bertrand
    14 hours ago











  • In general, you can use either the CONVERT() function with a suitable formatting code, and massage the result to your linking (chop off the beginning, end, replace unwanted characters with nothing etc). Or use FORMAT() with which you can specify a "pattern" for the returned string. Format uses more CPU, though.
    – Tibor Karaszi
    14 hours ago










  • How are you using CONCAT() in SQL Server 2008? Also, please read this about your convert() calls.
    – Aaron Bertrand
    13 hours ago










  • I don't know how it is working but I tried and it worked.
    – T.H.
    11 hours ago











  • Then you're not using SQL Server 2008 (which is how you tagged your question), since CONCAT() was introduced in SQL Server 2012.
    – Aaron Bertrand
    10 hours ago
















up vote
2
down vote

favorite












I want to get the date in DDMMYYY format from a field that stores date in YYYY-MM-DD Timestamp format. (For example, '2018-11-09' should display '09Nov2018').



I know that one way is to use the datepart function (https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017) which can extract the year, month and day individually and then I can concat them.



Edit: I don't want to use getdate(). There is a column which has YYYY-MM-DD Timestamp format and from that column I am extracting 'DDMM2018'. I am using:



concat
(
CONVERT(varchar, DATEPART(dd,columndate)),
CONVERT(varchar, LEFT(DATENAME(month, DATEPART(month,columndate)),3)) ,
CONVERT(varchar, DATEPART(year, columndate))
) AS trial


This gives me '9Nov2018' and not '09Nov2018'. I am planning to convert this back to `datetype' again as that is how I want the result.



Is there any other way to achieve this?



Any ideas/suggestions much appreciated.










share|improve this question



















  • 1




    If the data type is date, then it doesn't store it in any format, that is just how SSMS chooses to show it to you.
    – Aaron Bertrand
    14 hours ago











  • In general, you can use either the CONVERT() function with a suitable formatting code, and massage the result to your linking (chop off the beginning, end, replace unwanted characters with nothing etc). Or use FORMAT() with which you can specify a "pattern" for the returned string. Format uses more CPU, though.
    – Tibor Karaszi
    14 hours ago










  • How are you using CONCAT() in SQL Server 2008? Also, please read this about your convert() calls.
    – Aaron Bertrand
    13 hours ago










  • I don't know how it is working but I tried and it worked.
    – T.H.
    11 hours ago











  • Then you're not using SQL Server 2008 (which is how you tagged your question), since CONCAT() was introduced in SQL Server 2012.
    – Aaron Bertrand
    10 hours ago












up vote
2
down vote

favorite









up vote
2
down vote

favorite











I want to get the date in DDMMYYY format from a field that stores date in YYYY-MM-DD Timestamp format. (For example, '2018-11-09' should display '09Nov2018').



I know that one way is to use the datepart function (https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017) which can extract the year, month and day individually and then I can concat them.



Edit: I don't want to use getdate(). There is a column which has YYYY-MM-DD Timestamp format and from that column I am extracting 'DDMM2018'. I am using:



concat
(
CONVERT(varchar, DATEPART(dd,columndate)),
CONVERT(varchar, LEFT(DATENAME(month, DATEPART(month,columndate)),3)) ,
CONVERT(varchar, DATEPART(year, columndate))
) AS trial


This gives me '9Nov2018' and not '09Nov2018'. I am planning to convert this back to `datetype' again as that is how I want the result.



Is there any other way to achieve this?



Any ideas/suggestions much appreciated.










share|improve this question















I want to get the date in DDMMYYY format from a field that stores date in YYYY-MM-DD Timestamp format. (For example, '2018-11-09' should display '09Nov2018').



I know that one way is to use the datepart function (https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2017) which can extract the year, month and day individually and then I can concat them.



Edit: I don't want to use getdate(). There is a column which has YYYY-MM-DD Timestamp format and from that column I am extracting 'DDMM2018'. I am using:



concat
(
CONVERT(varchar, DATEPART(dd,columndate)),
CONVERT(varchar, LEFT(DATENAME(month, DATEPART(month,columndate)),3)) ,
CONVERT(varchar, DATEPART(year, columndate))
) AS trial


This gives me '9Nov2018' and not '09Nov2018'. I am planning to convert this back to `datetype' again as that is how I want the result.



Is there any other way to achieve this?



Any ideas/suggestions much appreciated.







sql-server sql-server-2008






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited 13 hours ago









Aaron Bertrand

148k18279476




148k18279476










asked 14 hours ago









T.H.

855




855







  • 1




    If the data type is date, then it doesn't store it in any format, that is just how SSMS chooses to show it to you.
    – Aaron Bertrand
    14 hours ago











  • In general, you can use either the CONVERT() function with a suitable formatting code, and massage the result to your linking (chop off the beginning, end, replace unwanted characters with nothing etc). Or use FORMAT() with which you can specify a "pattern" for the returned string. Format uses more CPU, though.
    – Tibor Karaszi
    14 hours ago










  • How are you using CONCAT() in SQL Server 2008? Also, please read this about your convert() calls.
    – Aaron Bertrand
    13 hours ago










  • I don't know how it is working but I tried and it worked.
    – T.H.
    11 hours ago











  • Then you're not using SQL Server 2008 (which is how you tagged your question), since CONCAT() was introduced in SQL Server 2012.
    – Aaron Bertrand
    10 hours ago












  • 1




    If the data type is date, then it doesn't store it in any format, that is just how SSMS chooses to show it to you.
    – Aaron Bertrand
    14 hours ago











  • In general, you can use either the CONVERT() function with a suitable formatting code, and massage the result to your linking (chop off the beginning, end, replace unwanted characters with nothing etc). Or use FORMAT() with which you can specify a "pattern" for the returned string. Format uses more CPU, though.
    – Tibor Karaszi
    14 hours ago










  • How are you using CONCAT() in SQL Server 2008? Also, please read this about your convert() calls.
    – Aaron Bertrand
    13 hours ago










  • I don't know how it is working but I tried and it worked.
    – T.H.
    11 hours ago











  • Then you're not using SQL Server 2008 (which is how you tagged your question), since CONCAT() was introduced in SQL Server 2012.
    – Aaron Bertrand
    10 hours ago







1




1




If the data type is date, then it doesn't store it in any format, that is just how SSMS chooses to show it to you.
– Aaron Bertrand
14 hours ago





If the data type is date, then it doesn't store it in any format, that is just how SSMS chooses to show it to you.
– Aaron Bertrand
14 hours ago













In general, you can use either the CONVERT() function with a suitable formatting code, and massage the result to your linking (chop off the beginning, end, replace unwanted characters with nothing etc). Or use FORMAT() with which you can specify a "pattern" for the returned string. Format uses more CPU, though.
– Tibor Karaszi
14 hours ago




In general, you can use either the CONVERT() function with a suitable formatting code, and massage the result to your linking (chop off the beginning, end, replace unwanted characters with nothing etc). Or use FORMAT() with which you can specify a "pattern" for the returned string. Format uses more CPU, though.
– Tibor Karaszi
14 hours ago












How are you using CONCAT() in SQL Server 2008? Also, please read this about your convert() calls.
– Aaron Bertrand
13 hours ago




How are you using CONCAT() in SQL Server 2008? Also, please read this about your convert() calls.
– Aaron Bertrand
13 hours ago












I don't know how it is working but I tried and it worked.
– T.H.
11 hours ago





I don't know how it is working but I tried and it worked.
– T.H.
11 hours ago













Then you're not using SQL Server 2008 (which is how you tagged your question), since CONCAT() was introduced in SQL Server 2012.
– Aaron Bertrand
10 hours ago




Then you're not using SQL Server 2008 (which is how you tagged your question), since CONCAT() was introduced in SQL Server 2012.
– Aaron Bertrand
10 hours ago










2 Answers
2






active

oldest

votes

















up vote
5
down vote



accepted










Here is an example using GETDATE():



SELECT REPLACE(CONVERT(char(11), GETDATE(), 13),' ','');


Yields 12Nov2018. Three days ago, it would yield 09Nov2018.



To run this against your table:



SELECT Trial = REPLACE(CONVERT(char(11), YOUR_COLUMN_NAME_HERE, 13),' ','')
FROM dbo.YOUR_TABLE_NAME_HERE;


In SQL Server 2012 and above, you can use FORMAT() but, like Tibor suggested, this uses more CPU (roughly doubling runtime in my tests).



SELECT FORMAT(GETDATE(), 'ddMMMyyyy');





share|improve this answer






















  • Thank you for the answer but I can't use this as my reference date is not getdate(). There is a column which has YYYY-MM-DD Timestamp format and from that column I am extracting 'DDMM2018'. I am using concat(CONVERT(varchar, DATEPART(dd,columndate)), CONVERT(varchar, LEFT(DATENAME(month, DATEPART(month,columndate)),3)) , CONVERT(varchar, DATEPART(year, columndate))) AS trial. This gives me '9Nov2018' and not '09Nov2018'. I am planning to convert this back to datetype again as that is how I want the result.
    – T.H.
    13 hours ago






  • 1




    @T.H. you replace GETDATE() in my statement with the column name in your query. Your question didn't tell us either the name of the column or the name of the table, so I showed a sample that worked against a function instead.
    – Aaron Bertrand
    13 hours ago











  • Apologies about that, I will update the question. Thanks a lot.
    – T.H.
    13 hours ago

















up vote
0
down vote













SELECT Replace(Convert(nvarchar(100),YourDate,106),' ','') gives the desired result
enter image description here






share|improve this answer








New contributor




Momo is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

















    Your Answer








    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "182"
    ;
    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',
    convertImagesToLinks: false,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    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%2fdba.stackexchange.com%2fquestions%2f222342%2fhow-to-get-the-date-part-from-a-date-with-timestamp-column-in-sql-server%23new-answer', 'question_page');

    );

    Post as a guest






























    2 Answers
    2






    active

    oldest

    votes








    2 Answers
    2






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    5
    down vote



    accepted










    Here is an example using GETDATE():



    SELECT REPLACE(CONVERT(char(11), GETDATE(), 13),' ','');


    Yields 12Nov2018. Three days ago, it would yield 09Nov2018.



    To run this against your table:



    SELECT Trial = REPLACE(CONVERT(char(11), YOUR_COLUMN_NAME_HERE, 13),' ','')
    FROM dbo.YOUR_TABLE_NAME_HERE;


    In SQL Server 2012 and above, you can use FORMAT() but, like Tibor suggested, this uses more CPU (roughly doubling runtime in my tests).



    SELECT FORMAT(GETDATE(), 'ddMMMyyyy');





    share|improve this answer






















    • Thank you for the answer but I can't use this as my reference date is not getdate(). There is a column which has YYYY-MM-DD Timestamp format and from that column I am extracting 'DDMM2018'. I am using concat(CONVERT(varchar, DATEPART(dd,columndate)), CONVERT(varchar, LEFT(DATENAME(month, DATEPART(month,columndate)),3)) , CONVERT(varchar, DATEPART(year, columndate))) AS trial. This gives me '9Nov2018' and not '09Nov2018'. I am planning to convert this back to datetype again as that is how I want the result.
      – T.H.
      13 hours ago






    • 1




      @T.H. you replace GETDATE() in my statement with the column name in your query. Your question didn't tell us either the name of the column or the name of the table, so I showed a sample that worked against a function instead.
      – Aaron Bertrand
      13 hours ago











    • Apologies about that, I will update the question. Thanks a lot.
      – T.H.
      13 hours ago














    up vote
    5
    down vote



    accepted










    Here is an example using GETDATE():



    SELECT REPLACE(CONVERT(char(11), GETDATE(), 13),' ','');


    Yields 12Nov2018. Three days ago, it would yield 09Nov2018.



    To run this against your table:



    SELECT Trial = REPLACE(CONVERT(char(11), YOUR_COLUMN_NAME_HERE, 13),' ','')
    FROM dbo.YOUR_TABLE_NAME_HERE;


    In SQL Server 2012 and above, you can use FORMAT() but, like Tibor suggested, this uses more CPU (roughly doubling runtime in my tests).



    SELECT FORMAT(GETDATE(), 'ddMMMyyyy');





    share|improve this answer






















    • Thank you for the answer but I can't use this as my reference date is not getdate(). There is a column which has YYYY-MM-DD Timestamp format and from that column I am extracting 'DDMM2018'. I am using concat(CONVERT(varchar, DATEPART(dd,columndate)), CONVERT(varchar, LEFT(DATENAME(month, DATEPART(month,columndate)),3)) , CONVERT(varchar, DATEPART(year, columndate))) AS trial. This gives me '9Nov2018' and not '09Nov2018'. I am planning to convert this back to datetype again as that is how I want the result.
      – T.H.
      13 hours ago






    • 1




      @T.H. you replace GETDATE() in my statement with the column name in your query. Your question didn't tell us either the name of the column or the name of the table, so I showed a sample that worked against a function instead.
      – Aaron Bertrand
      13 hours ago











    • Apologies about that, I will update the question. Thanks a lot.
      – T.H.
      13 hours ago












    up vote
    5
    down vote



    accepted







    up vote
    5
    down vote



    accepted






    Here is an example using GETDATE():



    SELECT REPLACE(CONVERT(char(11), GETDATE(), 13),' ','');


    Yields 12Nov2018. Three days ago, it would yield 09Nov2018.



    To run this against your table:



    SELECT Trial = REPLACE(CONVERT(char(11), YOUR_COLUMN_NAME_HERE, 13),' ','')
    FROM dbo.YOUR_TABLE_NAME_HERE;


    In SQL Server 2012 and above, you can use FORMAT() but, like Tibor suggested, this uses more CPU (roughly doubling runtime in my tests).



    SELECT FORMAT(GETDATE(), 'ddMMMyyyy');





    share|improve this answer














    Here is an example using GETDATE():



    SELECT REPLACE(CONVERT(char(11), GETDATE(), 13),' ','');


    Yields 12Nov2018. Three days ago, it would yield 09Nov2018.



    To run this against your table:



    SELECT Trial = REPLACE(CONVERT(char(11), YOUR_COLUMN_NAME_HERE, 13),' ','')
    FROM dbo.YOUR_TABLE_NAME_HERE;


    In SQL Server 2012 and above, you can use FORMAT() but, like Tibor suggested, this uses more CPU (roughly doubling runtime in my tests).



    SELECT FORMAT(GETDATE(), 'ddMMMyyyy');






    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited 13 hours ago

























    answered 14 hours ago









    Aaron Bertrand

    148k18279476




    148k18279476











    • Thank you for the answer but I can't use this as my reference date is not getdate(). There is a column which has YYYY-MM-DD Timestamp format and from that column I am extracting 'DDMM2018'. I am using concat(CONVERT(varchar, DATEPART(dd,columndate)), CONVERT(varchar, LEFT(DATENAME(month, DATEPART(month,columndate)),3)) , CONVERT(varchar, DATEPART(year, columndate))) AS trial. This gives me '9Nov2018' and not '09Nov2018'. I am planning to convert this back to datetype again as that is how I want the result.
      – T.H.
      13 hours ago






    • 1




      @T.H. you replace GETDATE() in my statement with the column name in your query. Your question didn't tell us either the name of the column or the name of the table, so I showed a sample that worked against a function instead.
      – Aaron Bertrand
      13 hours ago











    • Apologies about that, I will update the question. Thanks a lot.
      – T.H.
      13 hours ago
















    • Thank you for the answer but I can't use this as my reference date is not getdate(). There is a column which has YYYY-MM-DD Timestamp format and from that column I am extracting 'DDMM2018'. I am using concat(CONVERT(varchar, DATEPART(dd,columndate)), CONVERT(varchar, LEFT(DATENAME(month, DATEPART(month,columndate)),3)) , CONVERT(varchar, DATEPART(year, columndate))) AS trial. This gives me '9Nov2018' and not '09Nov2018'. I am planning to convert this back to datetype again as that is how I want the result.
      – T.H.
      13 hours ago






    • 1




      @T.H. you replace GETDATE() in my statement with the column name in your query. Your question didn't tell us either the name of the column or the name of the table, so I showed a sample that worked against a function instead.
      – Aaron Bertrand
      13 hours ago











    • Apologies about that, I will update the question. Thanks a lot.
      – T.H.
      13 hours ago















    Thank you for the answer but I can't use this as my reference date is not getdate(). There is a column which has YYYY-MM-DD Timestamp format and from that column I am extracting 'DDMM2018'. I am using concat(CONVERT(varchar, DATEPART(dd,columndate)), CONVERT(varchar, LEFT(DATENAME(month, DATEPART(month,columndate)),3)) , CONVERT(varchar, DATEPART(year, columndate))) AS trial. This gives me '9Nov2018' and not '09Nov2018'. I am planning to convert this back to datetype again as that is how I want the result.
    – T.H.
    13 hours ago




    Thank you for the answer but I can't use this as my reference date is not getdate(). There is a column which has YYYY-MM-DD Timestamp format and from that column I am extracting 'DDMM2018'. I am using concat(CONVERT(varchar, DATEPART(dd,columndate)), CONVERT(varchar, LEFT(DATENAME(month, DATEPART(month,columndate)),3)) , CONVERT(varchar, DATEPART(year, columndate))) AS trial. This gives me '9Nov2018' and not '09Nov2018'. I am planning to convert this back to datetype again as that is how I want the result.
    – T.H.
    13 hours ago




    1




    1




    @T.H. you replace GETDATE() in my statement with the column name in your query. Your question didn't tell us either the name of the column or the name of the table, so I showed a sample that worked against a function instead.
    – Aaron Bertrand
    13 hours ago





    @T.H. you replace GETDATE() in my statement with the column name in your query. Your question didn't tell us either the name of the column or the name of the table, so I showed a sample that worked against a function instead.
    – Aaron Bertrand
    13 hours ago













    Apologies about that, I will update the question. Thanks a lot.
    – T.H.
    13 hours ago




    Apologies about that, I will update the question. Thanks a lot.
    – T.H.
    13 hours ago












    up vote
    0
    down vote













    SELECT Replace(Convert(nvarchar(100),YourDate,106),' ','') gives the desired result
    enter image description here






    share|improve this answer








    New contributor




    Momo is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
    Check out our Code of Conduct.





















      up vote
      0
      down vote













      SELECT Replace(Convert(nvarchar(100),YourDate,106),' ','') gives the desired result
      enter image description here






      share|improve this answer








      New contributor




      Momo is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
      Check out our Code of Conduct.



















        up vote
        0
        down vote










        up vote
        0
        down vote









        SELECT Replace(Convert(nvarchar(100),YourDate,106),' ','') gives the desired result
        enter image description here






        share|improve this answer








        New contributor




        Momo is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.









        SELECT Replace(Convert(nvarchar(100),YourDate,106),' ','') gives the desired result
        enter image description here







        share|improve this answer








        New contributor




        Momo is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.









        share|improve this answer



        share|improve this answer






        New contributor




        Momo is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.









        answered 11 hours ago









        Momo

        1




        1




        New contributor




        Momo is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.





        New contributor





        Momo is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.






        Momo is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
        Check out our Code of Conduct.



























             

            draft saved


            draft discarded















































             


            draft saved


            draft discarded














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f222342%2fhow-to-get-the-date-part-from-a-date-with-timestamp-column-in-sql-server%23new-answer', 'question_page');

            );

            Post as a guest













































































            Popular posts from this blog

            How to check contact read email or not when send email to Individual?

            Displaying single band from multi-band raster using QGIS

            How many registers does an x86_64 CPU actually have?