SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


date format in stored procedure


date format in stored procedure

Author
Message
kavyacb
kavyacb
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 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'.
ColdCoffee
ColdCoffee
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3691 Visits: 5549
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.
kavyacb
kavyacb
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 17
is that date vill be In mm/dd/yyyu format
ColdCoffee
ColdCoffee
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3691 Visits: 5549
Yes it will be.. but if u be bit more specific on what you need, then v might provide a better solution..
engr_faruque
engr_faruque
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 9
The Below SQL Command
------------
SELECT CONVERT (VARCHAR,GETDATE(),101) [Date]
Here GETDATE() is Date field of Table.
--
Out Put->mm/dd/yyyy
Ben Teraberry
Ben Teraberry
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1515 Visits: 1199
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search