Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

date format in stored procedure Expand / Collapse
Author
Message
Posted Monday, June 07, 2010 4:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 07, 2010 4:59 AM
Points: 8, Visits: 17
create procedure USP_InsertStatusText(@Statustext char(150),@RecievedBy varchar(50),@Deliverydate datetime)
as
BEGIN
declare @date datetime
set @Deliverydate=select @Deliverydate convert(varchar(),getdate(),1)as[mm/dd/yy] from TB_StatusEntry
insert into TB_StatusText values(@Statustext,@RecievedBy,@Deliverydate)
END


i want to convert the Delivery date to mm/dd/yyyy format but am getting error as
Msg 156, Level 15, State 1, Procedure USP_InsertStatusText, Line 5
Incorrect syntax near the keyword 'select'.
Post #933409
Posted Monday, June 07, 2010 4:33 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 07, 2014 3:41 PM
Points: 2,259, Visits: 5,395
Kavya,, try this:


create procedure USP_InsertStatusText(@Statustext char(150),@RecievedBy varchar(50),@Deliverydate datetime)
as
BEGIN
declare @date datetime

set @Deliverydate= convert(varchar,@Deliverydate,1) --as [mm/dd/yy] from TB_StatusEntry

insert into TB_StatusText values(@Statustext,@RecievedBy,@Deliverydate)

END

But i tell, you, please store the date field in DateTime and NOT in some other formats.
Post #933415
Posted Monday, June 07, 2010 4:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 07, 2010 4:59 AM
Points: 8, Visits: 17
is that date vill be In mm/dd/yyyu format
Post #933416
Posted Monday, June 07, 2010 5:09 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, April 07, 2014 3:41 PM
Points: 2,259, Visits: 5,395
Yes it will be.. but if u be bit more specific on what you need, then v might provide a better solution..
Post #933432
Posted Wednesday, June 09, 2010 3:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 14, 2010 12:43 AM
Points: 6, Visits: 9
The Below SQL Command
------------
SELECT CONVERT (VARCHAR,GETDATE(),101) [Date]
Here GETDATE() is Date field of Table.
--
Out Put->mm/dd/yyyy
Post #934486
Posted Wednesday, June 09, 2010 11:31 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, December 04, 2013 1:49 PM
Points: 1,104, Visits: 1,174
Couple 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:
declare @date datetime
set @date = CONVERT(varchar(10),getdate(),101)
select @date

returns
--2010-06-09 00:00:00.000

while
declare @date varchar(10)
set @date = CONVERT(varchar(10),getdate(),101)
select @date

returns
--06/09/2010

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.


└> bt


Forum Etiquette: How to post data/code on a forum to get the best help
Post #934811
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse