June 7, 2010 at 4:27 am
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'.
June 7, 2010 at 4:33 am
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.
June 7, 2010 at 4:38 am
is that date vill be In mm/dd/yyyu format
June 7, 2010 at 5:09 am
Yes it will be.. but if u be bit more specific on what you need, then v might provide a better solution..
June 9, 2010 at 3:40 am
The Below SQL Command
------------
SELECT CONVERT (VARCHAR,GETDATE(),101) [Date]
Here GETDATE() is Date field of Table.
--
Out Put->mm/dd/yyyy
June 9, 2010 at 11:31 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply