Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Error converting data type varchar to datetime in

Author  Topic 

archanakintu
Starting Member

1 Post

Posted - 2012-01-18 : 05:15:05
Hi Forum Member

I am Trying to converting data type varchar to datetime in sqlserver 2005. but it will not working..

my Actual Procedure is

USE [AFMS]
GO
/****** Object: StoredProcedure [dbo].[SelectAll_ExpenseDetails] Script Date: 01/05/2012 17:37:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Created By Archana
-- Create date: 09-12-2011
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SelectAll_ExpenseDetails]
@LNK_File_ID numeric(18, 0) = NULL,
@LNK_Client_ID numeric(18, 0) = NULL,
@ExpanseDate datetime = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT E.[ExpanseId]
,CONVERT(datetime,E.[ExpanseDate],103) as [ExpanseDate]
,E.[Amount]
,E.[LNK_File_ID]
,E.[LNK_SubFile_ID]
,E.[LNK_CreatedBy_ID]
,CONVERT(datetime,E.[CreatedOn],103) as [CreatedOn]
,c.Client_FirstName as ClientName
,f.FileNo
,f.FileName
FROM [AFMS].[dbo].[Expanse] E
JOIN [AFMS].[dbo].[Client] c on c.ClientId = E.[LNK_CreatedBy_ID]
JOIN [AFMS].[dbo].[File] f on f.FileId = E.[LNK_File_ID]
JOIN [AFMS].[dbo].[SubFile] sf on sf.SubFileId = E.[LNK_SubFile_ID]
Where
E.LNK_File_ID = Case when @LNK_File_ID IS NOT NULL THEN @LNK_File_ID
Else E.LNK_File_ID End and
E.[LNK_CreatedBy_ID]= Case when @LNK_Client_ID IS NOT NULL THEN @LNK_Client_ID
Else E.[LNK_CreatedBy_ID] End and
E.[ExpanseDate]= Case when @ExpanseDate IS NOT NULL THEN @ExpanseDate
Else E.[ExpanseDate] End
End


Execute Procedure

exec [SelectAll_ExpenseDetails]
@LNK_File_ID = null,
@LNK_Client_ID = null,
@ExpanseDate = '29/12/2011'


Error is


Msg 8114, Level 16, State 5, Procedure SelectAll_ExpenseDetails, Line 0
Error converting data type varchar to datetime.



Archana Mistry
Sr. Programmer
Kintudesigns.com

Kristen
Test

22859 Posts

Posted - 2012-01-18 : 05:41:17
"Error converting data type varchar to datetime."

When converting "string" dates to date or datetime datatype always use:

yyyymmdd (NO hyphens or slashes)

because that is the only format that SQL will parse UNAMBIGUOUSLY. Any other format of date string will be parsed based on the Server Config, and the language and Country of the currently logged on user - which may change in the future!

Try:

exec [SelectAll_ExpenseDetails]
@LNK_File_ID = null,
@LNK_Client_ID = null,
@ExpanseDate = '20111229'
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2012-01-18 : 05:46:34
You may want to change this style too:

Where
E.LNK_File_ID = Case when @LNK_File_ID IS NOT NULL THEN @LNK_File_ID
Else E.LNK_File_ID End and
E.[LNK_CreatedBy_ID]= Case when @LNK_Client_ID IS NOT NULL THEN @LNK_Client_ID
Else E.[LNK_CreatedBy_ID] End and
E.[ExpanseDate]= Case when @ExpanseDate IS NOT NULL THEN @ExpanseDate
Else E.[ExpanseDate] End

This will NOT include columns where the column value is NULL and the @Parameter is ALSO NULL. In this example it may be that all columns are defined as NOT NULL, in which case it will work OK, but for future reference and/or for other queries where a column might contain NULL values, then it would be better to do:


Where
(@LNK_File_ID IS NULL OR E.LNK_File_ID = @LNK_File_ID)
AND (@LNK_Client_ID IS NULL OR E.[LNK_CreatedBy_ID]= @LNK_Client_ID)
AND (@ExpanseDate IS NULL OR E.[ExpanseDate]= @ExpanseDate)

This type of "generic" query is not very efficient as it will usually mean that Indexes are not used, but it may be more important for you to have the flexibility in your query. If your database table is large and/or you have performance problems you will need to use something more sophisticated.
Go to Top of Page

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-18 : 09:17:02
quote:

When converting "string" dates to date or datetime datatype always use:

yyyymmdd (NO hyphens or slashes)


There are three unambiguous formats, not just one.

'YYYYMMDD'
'YYYYMMDD HH:MM:SS.mmm'
'YYYY-MM-DDTHH:MM:SS.mmm'


quote:

Where
(@LNK_File_ID IS NULL OR E.LNK_File_ID = @LNK_File_ID)
AND (@LNK_Client_ID IS NULL OR E.[LNK_CreatedBy_ID]= @LNK_Client_ID)
AND (@ExpanseDate IS NULL OR E.[ExpanseDate]= @ExpanseDate)

This type of "generic" query is not very efficient as it will usually mean that Indexes are not used, but it may be more important for you to have the flexibility in your query. If your database table is large and/or you have performance problems you will need to use something more sophisticated.


Gail shaw's blog post will help (bookmark it): http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Personally, I think dynamic sql is the way to go here, as long as it's done properly using paramaterised queries.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2012-01-18 : 13:08:31
quote:
Originally posted by Transact Charlie

quote:

When converting "string" dates to date or datetime datatype always use:

yyyymmdd (NO hyphens or slashes)


There are three unambiguous formats, not just one.

'YYYYMMDD'
'YYYYMMDD HH:MM:SS.mmm'
'YYYY-MM-DDTHH:MM:SS.mmm'




Yeah, sorry, I should have been more specific. I was referring exclusively to Dates as the O/P's example didn't have any time component.

"Personally, I think dynamic sql is the way to go here, as long as it's done properly using paramaterised queries."

I've got plenty of (@Param IS NULL OR MyColumn = @Param) queries. Over the years we have added more ... and more ... @Parameters as the client(s) have requested then ... and now we wish that we had done them ALL as Dynamic SQL from day one.

The other, somewhat, related issue we have is with the SELECT statements; we have added more ... and more ... columns as clients have requested / needed them, and not all clients use all columns, so we are retrieving unnecessary columns, so to make the SELECT statement more "configurable" client-by-client we are moving to dynamic SQL.

Made much easier in SQL 2008 (and maybe it was in SQL 2005 ?) by EXECUTE AS so that we don;t have to give SELECT permission to underlying tables, only EXECUTE permission on the Sproc, as before.
Go to Top of Page
   

- Advertisement -