InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 16:25:46 GMT20http://www.sqlservercentral.com/Forums/Topic933409-149-1.aspxCouple of basic things ...Generally you don't want to worry about formatting on the database side. The db should just be storing the date as a date ... the user interface is where the formatting should take place. If you have to format on the db side, what you're going to end up with after using the convert function is a varchar, so it won't work to store that in a datetime variable ... the datetime variable will not maintain your format, it will just be whatever the standard datetime format is on your system. For example, on my system:[code="sql"]declare @date datetimeset @date = CONVERT(varchar(10),getdate(),101)select @date[/code]returns[code="sql"]--2010-06-09 00:00:00.000[/code]while[code="sql"]declare @date varchar(10)set @date = CONVERT(varchar(10),getdate(),101)select @date[/code]returns[code="sql"]--06/09/2010[/code]Again though, you really don't want to store any dates as varchar/char fields ever ... you'll just end up with a mess.And also, this may seem trivial, but anywhere you're doing an insert into a table, you should specify the columns you're inserting into in the correct order. This will allow you to avoid problems should the column order of the table ever change.Wed, 09 Jun 2010 11:31:48 GMTbteraberryhttp://www.sqlservercentral.com/Forums/Topic933409-149-1.aspxThe Below SQL Command------------ SELECT CONVERT (VARCHAR,GETDATE(),101) [Date]Here GETDATE() is Date field of Table.--Out Put->mm/dd/yyyyWed, 09 Jun 2010 03:40:38 GMTengr_faruquehttp://www.sqlservercentral.com/Forums/Topic933409-149-1.aspxYes it will be.. but if u be bit more specific on what you need, then v might provide a better solution..Mon, 07 Jun 2010 05:09:24 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic933409-149-1.aspxis that date vill be In mm/dd/yyyu formatMon, 07 Jun 2010 04:38:44 GMTkavyacbhttp://www.sqlservercentral.com/Forums/Topic933409-149-1.aspxKavya,, try this:[code="sql"]create procedure USP_InsertStatusText(@Statustext char(150),@RecievedBy varchar(50),@Deliverydate datetime)asBEGINdeclare @date datetimeset @Deliverydate= convert(varchar,@Deliverydate,1) --as [mm/dd/yy] from TB_StatusEntryinsert into TB_StatusText values(@Statustext,@RecievedBy,@Deliverydate)END[/code]But i tell, you, please store the date field in DateTime and NOT in some other formats.Mon, 07 Jun 2010 04:33:16 GMTColdCoffeehttp://www.sqlservercentral.com/Forums/Topic933409-149-1.aspxcreate procedure USP_InsertStatusText(@Statustext char(150),@RecievedBy varchar(50),@Deliverydate datetime)asBEGINdeclare @date datetimeset @Deliverydate=select @Deliverydate convert(varchar(),getdate(),1)as[mm/dd/yy] from TB_StatusEntryinsert into TB_StatusText values(@Statustext,@RecievedBy,@Deliverydate)ENDi want to convert the Delivery date to mm/dd/yyyy format but am getting error asMsg 156, Level 15, State 1, Procedure USP_InsertStatusText, Line 5Incorrect syntax near the keyword 'select'.Mon, 07 Jun 2010 04:27:29 GMTkavyacb