Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
|
Forums » .NET » SQL Server »
Error in sql server when passing the date in format "MM/dd/yyyy HH:mm:ss tt"
Posted Date: 20 Aug 2008 Posted By: Priti Member Level: Bronze Points: 1
Responses:
4
|
I want the today's Date in the format "MM/dd/yyyy HH:mm:ss tt".What I have to do?How can I directly get this format in Sql using GetDate()
Below is the SP:
ALTER PROCEDURE [dbo].[proc_UpdateSavingplan_SavingAmount]
(@HyperionEntity NVARCHAR(10),
@SavingplanID [int],
@FiscalYear [int],
@Month [nvarchar](4),
@SavingAmount [int],
@DateUpdated datetime,
@LastUpdatedByUID [nvarchar](10)
)
AS
BEGIN
Declare @Qry varchar(2000)
Select @Qry = 'UPDATE [tblSavingplan] SET [DateUpdated] = ' + Cast(@DateUpdated as datetime) + ','
If @Month = 'AOct'
Select @Qry = @Qry + ' [AOct] = ' + @SavingAmount
If @Month = 'ANov'
Select @Qry = @Qry + ' [ANov] = ' + @SavingAmount
.
.
.
Select @Qry = @Qry + '[LastUpdatedByUID] = ' + @LastUpdatedByUID +
'WHERE SavingplanID = ' + @SavingplanID + 'AND
FiscalYear = ' + @FiscalYear + 'AND PlantID = (SELECT PlantID FROM tblPlants
WHERE HyperionEntity = ' + @HyperionEntity + ')'
exec(@Qry )
IF (@@ERROR <> 0)
RETURN 1
Else
Return 999
END
--exec[proc_UpdateSavingplan_SavingAmount] '0687',100,2008,'AOct',1000,'08/18/2008 20:12:12 AM','pass'
gives an error:Msg 8114, Level 16, State 5, Procedure proc_UpdateSavingplan_SavingAmount, Line 0
Error converting data type varchar to datetime.
|
Responses
|
| Author: balamurugan 20 Aug 2008 | Member Level: Gold | Rating:  Points: 1 |
hi,
select convert(varchar,getdate(),101)
OutPut:- 06/18/2008
select convert(varchar,getdate(),102)
OutPut:- 2008.06.18
select convert(varchar,getdate(),103)
OutPut:- 18/06/2008
select convert(varchar,getdate(),104)
OutPut:- 18.06.2008
select convert(varchar,getdate(),105)
OutPut:- 18-06-2008
select convert(varchar,getdate(),106)
OutPut:- 18 Jun 2008
select convert(varchar,getdate(),107)
OutPut:- Jun 18, 2008
select convert(varchar,getdate(),108)
OutPut:- 10:53:20
select convert(varchar,getdate(),109)
OutPut:- Jun 18 2008 10:53:30:507AM
select convert(varchar,getdate(),110)
OutPut:- 06-18-2008
select convert(varchar,getdate(),111)
OutPut:- 2008/06/18
select convert(varchar,getdate(),112)
OutPut:- 20080618
select convert(varchar,getdate(),113)
OutPut:- 18 Jun 2008 10:54:01:253
select convert(varchar,getdate(),114)
OutPut:- 10:54:08:473
select convert(varchar,getdate(),120))
OutPut:- 2008-06-18 10:54:15
| | Author: Nagarajan.L 20 Aug 2008 | Member Level: Gold | Rating: Points: 6 |
hi
here you change the DateUpdated parameter datetime to varchar.
then u can solve this problem. and remove cast method also in query.
i given hole procedure below
ALTER PROCEDURE [dbo].[proc_UpdateSavingplan_SavingAmount]
(@HyperionEntity NVARCHAR(10),
@SavingplanID [int],
@FiscalYear [int],
@Month [nvarchar](4),
@SavingAmount [int],
@DateUpdated varchar(25),
@LastUpdatedByUID [nvarchar](10)
)
AS
BEGIN
Declare @Qry varchar(2000)
Select @Qry = 'UPDATE [tblSavingplan] SET [DateUpdated] = ' + @DateUpdated + ','
If @Month = 'AOct'
Select @Qry = @Qry + ' [AOct] = ' + @SavingAmount
If @Month = 'ANov'
Select @Qry = @Qry + ' [ANov] = ' + @SavingAmount
.
.
.
Select @Qry = @Qry + '[LastUpdatedByUID] = ' + @LastUpdatedByUID +
'WHERE SavingplanID = ' + @SavingplanID + 'AND
FiscalYear = ' + @FiscalYear + 'AND PlantID = (SELECT PlantID FROM tblPlants
WHERE HyperionEntity = ' + @HyperionEntity + ')'
exec(@Qry )
IF (@@ERROR <> 0)
RETURN 1
Else
Return 999
END
try this,....
thanks
Nagarajan.L
| | Author: Priti 20 Aug 2008 | Member Level: Bronze | Rating: Points: 4 |
I have already tried this ,it gives me error :
In Database : DateUpdated = DateTime type & [AOct] = int type
Msg 245, Level 16, State 1, Procedure proc_UpdateSavingplan_SavingAmount, Line 19
Conversion failed when converting the varchar value 'UPDATE [tblSavingplan] SET [DateUpdated] = 08/18/2008 20:12:12 AM, [AOct] = ' to data type int.
plz help
| | Author: Priti 21 Aug 2008 | Member Level: Bronze | Rating: Points: 4 |
I have already tried this ,it gives me error :
In Database : DateUpdated = DateTime type & [AOct] = int type
Msg 245, Level 16, State 1, Procedure proc_UpdateSavingplan_SavingAmount, Line 19
Conversion failed when converting the varchar value 'UPDATE [tblSavingplan] SET [DateUpdated] = 08/18/2008 20:12:12 AM, [AOct] = ' to data type int.
plz help
|
| Post Reply |
|
|
|
 | | This thread is locked for new responses. Please post your comments and questions as a separate thread.
If required, refer to the URL of this page in your new post. |
|
|
|
|