Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Converting and storing a date in a string variable

Answered Converting and storing a date in a string variable

  • Sunday, January 06, 2013 8:39 PM
     
     
    Sign In to Vote
    0

    Hi Gurus,

    I thought this would be simple but from all the postings on this site and work I have tried to-date, it is more difficult than I imagined.  I want to convert getdate() to  mm/dd/yyyy format, add additional text and store it as a string variable in my table.  For example "Approved by JS 1/15/2013".  To-date, I can only store is as "Approved by JS 2013-01-06 "I am aware from reading many of the posts that SQL deals with dates in a unique fashion.

    Thank you.

    BLUE GOULDING

     

All Replies

  • Sunday, January 06, 2013 8:52 PM
     
     Answered Has Code
    Sign In to Vote
    1

    Hello,

    You can use the format option 101 = US format, when you convert the date to string to get mm/dd/yyyy format:

    DECLARE @string varchar(50);

SET @string = 'Approved b JS ' + CONVERT(varchar(10), GetDate(), 101)
SELECT @string;

    Olaf Helper

    Blog Xing

     
  • Sunday, January 06, 2013 8:57 PM
    Moderator
     
     Answered
    Sign In to Vote
    1

    Store the person who approved it in the ApprovedBy column and the date when it was approved in the date ApprovedOn column. Then you can do

    select ..., "Approved by " + ApprovedBy + " " + convert(varchar(10),ApprovedBy, 101)

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Sunday, January 06, 2013 9:42 PM
     
     
    Sign In to Vote
    0

    Thank you Naomi,

    Works perfect!

    Thank you

    Blue

    BLUE GOULDING

     
  • Monday, January 07, 2013 2:55 AM
     
     
    Sign In to Vote
    1

    >>  I want to convert getdate() to  mm/dd/yyyy format, add additional text and store it as a string variable in my table.  <<

    We use CAST (CURRENT_TIMESTAMP AS DATE) now; getdate() was 1970's UNIX/Sybase dialect. 

    >> For example "Approved by JS 1/15/2013".  To-date, I can only store is as "Approved by JS 2013-01-06 "I am aware from reading many of the posts that SQL deals with dates in a unique fashion. <<

    You are not thinking in SQL yet. All presentation display formatting is done in a presentation layer, not the database. The only display format for ANSI/ISO Standard SQL is ISO-8601, with “yyyy-mm-dd HH:mm:ss.sssssss” options; this is embedded in most of the ISO Standards, so it is hardly unique! 

    You should not be doing this at all! This is a presentation layer task. 

    Back in the 1970's with the original Sybase, there was a string function called CONVERT() which is still supported and used by bad SQL programmers. It lets you “talk that SQL like it was COBOL!”, because back then we did not understand tiered architecture and we writing monolithic application code in T-SQL. This is why MONEY has the punctuation marks for US $ in it! 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

     
  • Monday, January 07, 2013 3:43 AM
     
     Answered
    Sign In to Vote
    0
    

    Hi CELKO,

    Thanks for this post, it really helps to expand my understanding and knowledge of how SQL works.  I beleive that Naomi suggestion to use two columns - one for the date and one for the string variable, would allow one to apply all formatting in the presentation layer.

    Cheers,

    Blue

    

    BLUE GOULDING

    • Marked As Answer by someone7777 Monday, January 07, 2013 3:44 AM
    •