The conversion of a char data type to a datetime d

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)

The conversion of a char data type to a datetime d

Author

Topic

jbluestein
Starting Member

7 Posts

Posted - 2010-04-26 : 18:37:00

Hey there SQL server dudes & dudettes, I'm getting the error above on this bit of code I've tried to convert @p03 to datetime as well.... is it the between statement? any help appreciated...

declare @p02 varchar(25)
declare @p03 datetime
set @p02 = 'test'
set @p03 = '4/26/2010'

SELECT DISTINCT LOX.LEAD AS CNTRC_LEAD_ID_1,
LOX.CNTR_ORG_NO AS ORG_NO,
LOX.CNTR_ID AS CNTR_ID,
LOX.CNTR_SEQ_NO AS SEQ_NO
FROM dbo.T_GNRC_LEAD_XREF AS LOX

INNER JOIN dbo.T_GNRC_CNTR_ITM AS ITM
ON LOX.CNTR_ORG_NO = ITM.CNTR_ORG_NO
AND LOX.CNTR_ID = ITM.CNTR_ID
AND LOX.CNTR_SEQ_NO = ITM.CNTR_SEQ_NO
and @p03 BETWEEN ITM.EFF_FROM_DT AND ITM.EFF_THRU_DT
AND LOX.SEND_FLAG = 'Y'
INNER JOIN dbo.T_GNRC_CNTR AS CU1
ON CU1.CNTR_ORG_NO = LOX.CNTR_ORG_NO
AND CU1.CNTR_ID = LOX.CNTR_ID
AND CU1.CNTR_SEQ_NO = LOX.CNTR_SEQ_NO
AND CU1.CNTR_REF_NO LIKE '%SG-%'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-26 : 18:42:44

What data types are EFF_FROM_DT and EFF_THRU_DT in the T_GNRC_CNTR_ITM table? I'm thinking at least one of them is a varchar and that there are invalid dates in it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jbluestein
Starting Member

7 Posts

Posted - 2010-04-26 : 18:51:00

hey tara - they are both datetime, thanks for the response.

USE [GENPAPPS]
GO
/****** Object: Table [dbo].[T_GNRC_ITM_CNTRC] Script Date: 04/26/2010 15:50:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[T_GNRC_ITM_CNTRC](
[CNTRC_LEAD_ID] [char](6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EM_ITEM_NUM] [char](7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PRC_EFF_DT] [datetime] NOT NULL,
[PRC_END_DT] [datetime] NOT NULL,
[CNTRCT_PRC] [numeric](9, 4) NOT NULL,
[CNTRC_VAR] [numeric](6, 5) NOT NULL,
[CHRGBK_IND] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[UPDATE_TS] [datetime] NULL,
[UPDATE_BY] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[INSERT_TS] [datetime] NULL,
[INSERT_BY] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-26 : 18:55:35

Your table name and column names in the script are different than what you have in the query.

If they are datetime data type, then try: set @p03 = '26/4/2010'

If that works, then we can easily fix it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jbluestein
Starting Member

7 Posts

Posted - 2010-04-26 : 19:38:35

thanks, but same error

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-27 : 03:47:37

What happens when you try this?

set @p03 = '20100426'

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 03:58:08

what does below return you?

SELECT @@LANGUAGE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jbluestein
Starting Member

7 Posts

Posted - 2010-04-27 : 12:01:26

same error using different date styles, I even tried using convert function. @@language is us_english - thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 12:07:27

quote:
Originally posted by jbluestein

same error using different date styles, I even tried using convert function. @@language is us_english - thanks,

did you mean even Madhi's method didnt work for you?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-27 : 12:11:46

jbluestein, your query uses a different table name and column names than the script that you posted. Why is that? Is it actually a view?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jbluestein
Starting Member

7 Posts

Posted - 2010-04-27 : 12:27:13

right you are tara, I'll check those columns soon, got a meeting now - thanks so much - same error using mahdi's date format - I'll recheck the column definitions..... who names tables like that? sheesh!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-27 : 12:41:20

Make sure you also check the view definition. There could be code in there causing this error.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jbluestein
Starting Member

7 Posts

Posted - 2010-04-27 : 17:23:56

it was the column definition (defined in a similarly named table as char(10)) - I owe you one - if you're in SF, CA, lemme know, I owe you a cup of coffee! thanks again.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-27 : 17:26:32

I'm in SD, CA. Just a few hours drive if you really want to get me that cup of coffee.

Glad to help!

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

jbluestein
Starting Member

7 Posts

Posted - 2010-04-28 : 08:16:13

it will have to be virtual for now - thanks again -
http://www.txberry.com/news/20090512/coffee.jpg

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources