qwerty1204 Posted January 10, 2013 Report Posted January 10, 2013 I have a Date column in a table in this format YYYY-MM-DD while querying from that table and displaying the result in the output, I need to show it in this format: MM-DD-YYYY how can it be done in the SQL query??
Nisacharudu Posted January 10, 2013 Report Posted January 10, 2013 [quote name='arey ooo samba' timestamp='1357854041' post='1303087342'] I have a Date column in a table in this format YYYY-MM-DD while querying from that table and displaying the result in the output, I need to show it in this format: MM-DD-YYYY how can it be done in the SQL query?? [/quote] nuvvu use chese tool(toad, SQL Navigator) lo untundi aa setting
150bryant Posted January 10, 2013 Report Posted January 10, 2013 ALTDATE http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2.doc.sqlref%2Fsadate.htm
Nisacharudu Posted January 10, 2013 Report Posted January 10, 2013 [quote name='jamajacha' timestamp='1357854209' post='1303087371'] ALTDATE [url="http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2.doc.sqlref%2Fsadate.htm"]http://publib.boulde...lref/sadate.htm[/url] [/quote] good to know
Aamayakudu Posted January 10, 2013 Report Posted January 10, 2013 TOCHAR ani yedhoo untundhi bhaiya ... chudu ...like TOCHAR(YYYY?MM?DD)ani untundhi
Desamudhuru Posted January 10, 2013 Report Posted January 10, 2013 convert(varchar(10), columnname,101) it will work i'm using this.
Aamayakudu Posted January 10, 2013 Report Posted January 10, 2013 select ename, hiredate, to_char((hiredate),'fmD') "Day" from emp order by "Day";
Ranga_The_Donga Posted January 10, 2013 Report Posted January 10, 2013 nuvu adigina format number.. 101
kranthi111 Posted January 10, 2013 Report Posted January 10, 2013 try this.. select CONVERT(VARCHAR(19),GETDATE(),110)
150bryant Posted January 10, 2013 Report Posted January 10, 2013 dude do the following .. char(date_column,USA) then replace the / with - as per your requirement. http://hotrecruiter.com/resources/technical-interviews/51-database-administration/1110-db2-dates-interview-questions
mrudhula99 Posted January 10, 2013 Report Posted January 10, 2013 [quote name='jamajacha' timestamp='1357854529' post='1303087424'] dude do the following .. char(date_column,USA) then replace the / with - as per your requirement. [url="http://hotrecruiter.com/resources/technical-interviews/51-database-administration/1110-db2-dates-interview-questions"]http://hotrecruiter....rview-questions[/url] [/quote] ala ela replace chesthaav...
150bryant Posted January 10, 2013 Report Posted January 10, 2013 [color=#333333][font=Calibri,]Q.I want to fetch a date column with the 'DD-MMM-YYYY' format. Can I do it in a single query? I tried to run the following SQL, but got an error: [/font][/color] [color=#333333][font=Calibri,] SELECT CHAR(T1.COL_DT ,'DD-MMM-YYYY'), [/font][/color] [color=#333333][font=Calibri,] FROM TABLE1 T1; [/font][/color] [color=#333333][font=Calibri,]what is the solution?[/font][/color] [color=#333333][font=Calibri,]A.You are not using the CHAR function correctly. When using CHAR to convert a date column into a character representation, the second parameter can only be one of the following: ISO, USA, EUR, JIS, or LOCAL. Consult Table 1 for the format to which each of these corresponds.To return the data in the format you are requesting, DD-MMM-YYYY, you will need to install a local date format routine. Get in touch with your system programmer to discuss whether this is feasible and how to do it.[/font][/color] [color=#333333][font=Calibri,]as per this question[/font][/color] [color=#333333][font=Calibri,]try CHAR(date_column,LOCAL)[/font][/color]
tom bhayya Posted January 10, 2013 Report Posted January 10, 2013 [quote name='mrudhula99' timestamp='1357854598' post='1303087434'] ala ela replace chesthaav... [/quote] cheppaka pothey em chesthav
150bryant Posted January 10, 2013 Report Posted January 10, 2013 [quote name='mrudhula99' timestamp='1357854598' post='1303087434'] ala ela replace chesthaav... [/quote] as per this : [color=#333333][font=Calibri,]Q.My format does not fit into any of these listed in question 1. What if I have a DATE stored like YYYYMMDD (with no dashes or slashes) and I want to compare it to a DB2 date? [/font][/color] [color=#333333][font=Calibri,]A.First of all you indicate that your date column contains dates in the following format: yyyymmdd with no dashes or slashes. You do not indicate whether this field is a numeric or character field - Assume that it is character. If it is not you can use the CHAR function to convert it to a character string. [/font][/color] [color=#333333][font=Calibri,]Then, you can use the SUBSTR function to break the character column apart into the separate components, for example SUBSTR(column,1,4) returns the year component, SUBSTR(column,5,2) returns the month, and SUBSTR(column,7,2) returns the day.Then you can concatenate all of these together into a format that DB2 recognizes, for example, the USA format which is mm/DD/yyyy. This can be done as follows: [/font][/color] [color=#333333][font=Calibri,] SUBSTR(column,5,2) || "/" || SUBSTR(column,7,2) || "/" || SUBSTR(column,1,4) [/font][/color] [color=#333333][font=Calibri,]Then you can use the DATE function to convert this character string into a DATE that DB2 will recognize. This is done as follows: [/font][/color] [color=#333333][font=Calibri,] DATE(SUBSTR(column,5,2) || "/" || SUBSTR(column,7,2) || "/" || [/font][/color] [color=#333333][font=Calibri,] SUBSTR(column,1,4))[/font][/color]
Recommended Posts