Sunday, January 06, 2013 8:39 PM
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.
Sunday, January 06, 2013 8:52 PM
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 HelperBlog Xing
Sunday, January 06, 2013 8:57 PMModerator
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
Sunday, January 06, 2013 9:42 PM
Thank you Naomi,
Monday, January 07, 2013 2:55 AM
>> 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
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.
- Marked As Answer by someone7777 Monday, January 07, 2013 3:44 AM