InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 26 May 2013 03:24:51 GMT20http://www.sqlservercentral.com/Forums/Topic1392931-391-1.aspxWe ask for table and data so that we can write a T-SQL statement and test it to ensure that what we do is what your require.For reference please read the link in my signature on posting code and data for the best help.Wed, 05 Dec 2012 06:39:21 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1392931-391-1.aspx[quote][b]Minnu (12/5/2012)[/b][hr]Hi Team,Am using below code to display name and "Date of birth"but Field DOB is not coming in Date format.[b]select @FinalString=(SELECT RTRIM(+ISNULL('Name ','')+' : ' +ISNULL(+CAST(NAME AS VARCHAR(100))+' ','') +ISNULL(' DOB :'+CAST(DOB AS VARCHAR(100))+' ','')) FROM ETCOL [/b]i want date format in below format.[b]rtrim(convert(varchar, DOB,107)) + ')' [/b]Can u please help me .....[/quote]If you want it formated like [code="sql"] rtrim(convert(varchar, DOB,107)) [/code] then use it in the select statement?[code="sql"] select @FinalString=(SELECT RTRIM(+ISNULL('Name ','')+' : ' + ISNULL(+CAST(NAME AS VARCHAR(100))+' ','') + ISNULL(' DOB :'+rtrim(convert(varchar(10), DOB,107)) +' ','')) FROM ETCOL[/code]I prefer to be explicit with my CONVERT(VARCHAR(12), DOB,107), since you are going to get a 30 character varchar variable by default, instead of the 12 character that you need.Wed, 05 Dec 2012 06:29:17 GMTDiverKashttp://www.sqlservercentral.com/Forums/Topic1392931-391-1.aspxHi Team,Table and data is normat,in my query am checking whether it is null or not null and am not converting to normal date format.can u please convert it to normal date if it is not null.Please help me.....Wed, 05 Dec 2012 06:27:02 GMTMinnuhttp://www.sqlservercentral.com/Forums/Topic1392931-391-1.aspxAnd now some data?Wed, 05 Dec 2012 06:11:04 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1392931-391-1.aspxCREATE TABLE ETCOL (NAME VARCHAR (50), DOB DATETIME)Wed, 05 Dec 2012 06:08:22 GMTMinnuhttp://www.sqlservercentral.com/Forums/Topic1392931-391-1.aspxPlease post the create table definition along with data for the table so what we can build up the string for you.Wed, 05 Dec 2012 06:05:58 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1392931-391-1.aspxBEGINselect @FinalString =(SELECT RTRIM(+ISNULL('Name','')+' : ' +ISNULL(+CAST(Name AS VARCHAR(100))+' ','')) FROM ETCOL WHERE RIGHT_TEXT ='Name') +CHAR(10)ENDELSEBEGINselect @FinalString=(SELECT RTRIM(+ISNULL('Name','')+' : ' +ISNULL(+CAST(Name AS VARCHAR(100))+' ','') +ISNULL(' DOB :'+CAST(DOB AS VARCHAR(100))+' ',''))FROM ETCOL WHERE RIGHT_TEXT ='Name') +CHAR(10)END ----------If the DOB is not null, then i want the date format like : Sep 30, 2012 Wed, 05 Dec 2012 06:04:30 GMTMinnuhttp://www.sqlservercentral.com/Forums/Topic1392931-391-1.aspxWell that converts perfectly.Can you provide the DDL and sample data for table ETCOL along with your full query?Wed, 05 Dec 2012 05:35:39 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1392931-391-1.aspxDate format in DOB Column is : '2012-09-30 00:00:00.000'i want date format in : Sep 30, 2012Wed, 05 Dec 2012 05:32:08 GMTMinnuhttp://www.sqlservercentral.com/Forums/Topic1392931-391-1.aspxCan you provide some sample data of the format that the DOB is and how it should be formattedWed, 05 Dec 2012 05:29:44 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1392931-391-1.aspxHi Team,Am using below code to display name and "Date of birth"but Field DOB is not coming in Date format.[b]select @FinalString=(SELECT RTRIM(+ISNULL('Name ','')+' : ' +ISNULL(+CAST(NAME AS VARCHAR(100))+' ','') +ISNULL(' DOB :'+CAST(DOB AS VARCHAR(100))+' ','')) FROM ETCOL [/b]i want date format in below format.[b]rtrim(convert(varchar, DOB,107)) + ')' [/b]Can u please help me .....Wed, 05 Dec 2012 05:18:49 GMTMinnu