Datetime conversion error

[ConChoona]

Hi,

When I run the following on one server, it runs correctly and adds the entry to the database:

exec sp_executesql N'INSERT INTO [BO_DEMO].[dbo].[CXV_LIST] ([LIST_UNO], [CREATED], [CREATED_BY], [LAST_MODIFIED_BY], [NAME], [START_DATE], [END_DATE], [OWNER_UNO], [SPONSOR_UNO], [LIST_TYPE_UNO], [DISCRIMINATOR], [INACTIVE], [LAST_MODIFIED], [ISSUE_DATE]) VALUES (@ListUno, @Created, @CreatedBy, @LastModifiedBy, @Name, @StartDate, @EndDate, @OwnerUno, @SponsorUno, @ListTypeUno, @Discriminator, @Inactive, @LastModified, @IssueDate)',N'@ListUno int,@Created datetime,@CreatedBy int,@LastModifiedBy int,@Name varchar(100),@StartDate datetime,@EndDate datetime,@OwnerUno int,@SponsorUno int,@ListTypeUno int,@Discriminator char(1),@Inactive char(1),@LastModified datetime,@IssueDate datetime',@ListUno=1,@Created='2008-07-15 22:41:08:777',@CreatedBy=4,@LastModifiedBy=4,@Name='TEST',@StartDate='2008-07-15 08:00:00:000',@EndDate='2008-07-15 08:30:00:000',@OwnerUno=33,@SponsorUno=33,@ListTypeUno=7,@Discriminator='P',@Inactive='N',@LastModified='2008-07-15 22:41:08:777',@IssueDate='2008-07-15 22:41:02:763'

However, if I run it on another server, on a slightly different db I get the following:

Conversion failed when converting datetime from character string.

Now, I'm not a programmer, so I am wondering if this is a problem with the SQL server configuration or something set incorrectly within the database setup? It should work on the second database, as they are essentially the same barring some transaction differences.

Cheers,

Con

 

[bborissov]

Your DATEFORMAT is different, When you pass datetime as string always use ISO format:
yyyymmdd hh:mm:ss:nnn
Try this:

DECLARE @Test varchar(50)
SET @Test = '2008-07-15 22:41:02:763'
SET DATEFORMAT MDY
SELECT CAST(@Test as datetime)
SET DATEFORMAT DMY
SELECT CAST(@Test as datetime) -- BOOM

-- BUT
SET @Test = '20080715 22:41:02:763'
SET DATEFORMAT MDY
SELECT CAST(@Test as datetime)
SET DATEFORMAT DMY
SELECT CAST(@Test as datetime) -- BOOM

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[ConChoona]

Hi Borislav,

Thanks for that - if this were the problem would it not work on the other server/db as well?

I didn't write this, but need to try and get to the bottom of it.

 

[gmmastros]

thread183-1240616

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ConChoona]

Hey gmmastros

Unfortunately both return a config value of 0

Thanks tho!

 

[gmmastros]

The language is dependent on the login. Is it possible that the code is failing because you are using a different login than the one you tested?

For SQL2000

Select Name, Language From master.sysxlogins

For SQL2005:

select Name, Language from master.sys.syslogins

Are all of the logins set for the same language? If not, you may have just found your problem.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

Oops,
Sorry, the second BOOM is not right
That should work no matter what Dateformat is set to:

DECLARE @Test varchar(50)
SET @Test = '2008-07-15 22:41:02:763'
SET DATEFORMAT MDY
SELECT CAST(@Test as datetime)
SET DATEFORMAT DMY
SELECT CAST(@Test as datetime) -- BOOM

-- BUT
SET @Test = '20080715 22:41:02:763'
SET DATEFORMAT MDY
SELECT CAST(@Test as datetime)
SET DATEFORMAT DMY
SELECT CAST(@Test as datetime) -- NO BOOM :-)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[ConChoona]

Hey guys - just to say thank you for your help with this.

Turns out there was a problem with the order of the columns within the table and insert (bad programming there I think!)

Anyway - thanks a lot for all your help - really appreciated.

C