DateTime Format -

I have installed the trial version of windows server 2003 on the second hard drive on my computer. I set up IIS and ran my website on it but the problem is when I do something on the site, which has a sqlinsert statement regarding datetime.now it says, "conversion failed when converting datetime from character string"

I think it's to do with the clock on server 2003, the format is like: 11/07/2007 2:39:59 a.m.

I think it should be in formatAM and not a.m.

Any ideas on how to change the time format on a computer?

Or should I just change the Columns in my table to a Nvarcher value or something?

thanks

how is the value coming through? from your application? via now() ?

|||

Hi,

Thanks for your reply

What do you mean via now()?

I'm using VB and if I use something like. sqldatasource1.insertparameters.add("enddate", datetime.now()) it will give the format: 11/07/2007 2:39:59a.m.(which gives the incorrect string error.) when it should be 11/07/2007 2:39:59AM,

It must be to do with the computer clocks date time format, on server 2003 ?

Any ideas?

|||

Hi,

Please run the "Regional and Language Options" in your Control Panel. Click on "Customize", and switch to the Time tab, just to modify the "AM symbol" and "PM symbol" and hava a try.

Good Luck.

|||

store the datetime column in international format or use now.tostring("format eg. MM/dd/yyyy hh:mm:ss etc ")

|||

If the SqlDbType = DateTime then format should not come into it as the output string display is just a human readable format for display use that is not used by SQL when feeding DateTime values into it.

Do you have a snippet of the code? Something like this is what I would expect for a successful date insertion: (example routine)

public static bool InsertDateIntoRandomTable() {bool blSuccess =false;string strComm ="INSERT INTO [RandomTable] " +"(One_Date) VALUES (@.One_Date)"; SqlConnection sqlConn =new SqlConnection(strGlobalSQLConnection); SqlCommand sqlComm =new SqlCommand(strComm, sqlConn); sqlComm.Parameters.Add("@.One_Date", SqlDbType.DateTime).Value = DateTime.Now; sqlConn.Open();if (sqlComm.ExecuteNonQuery() > 0) blSuccess =true; sqlConn.Close();return blSuccess; }

Hope this helps

Mark

|||

Hi,

Thanks for the help guys

I tried what you said and it changed the clock on the computer OK. But strangley, on the website; it is still doing the format 11/11/2006 12:07a.m. instead of 11/11/2006 12:07AM

Is it something to do with IIS settings?

Thanks

|||

Hi,

After you change the time format in Regional and Language Options, you shouldrestartthe Visual Studio and open your application project, build and run the application again. Then check it and explorer the page in your IIS.

Thanks.

|||

Thanks a lot for your help. I tried restarting my computer etc. but, no luck...

datetime convertion in SqlServer 2000

SqlServer 2000 with SP3. RepTime is a datetime in TableA. I ran the two commands beblow:

SELECT distinct CAST([RepTime] AS INT) FROM TableA

39004
39002
39003

select max(RepTime),min(RepTime) from TableA

2006-10-16 10:36:03.940 2006-10-13 17:32:00.080

From 2006-10-13 to 2006-10-16, there are four days. But I got three distinct int from it. Anyone knows ?

Maybe you have two entries having the same date but different times?

DECLARE @.test AS TABLE (dat datetime);

INSERT into @.test values ('2006-12-19')
INSERT into @.test values ('2006-12-20')
INSERT into @.test values (GETDATE())

-- This retrieves 3 columns
SELECT DISTINCT dat FROM @.test

-- This retrieves 2 columns
SELECT DISTINCT CAST(dat AS INT) FROM @.test

That's because converting to INT don't care of Hours/Minutes/Seconds, just using the Days to separate.

|||

Very interesting while dig in to your issue..

SQL Server Converts the date using the following logic...

Declare @.Date as Datetime
Select @.Date = '2006-10-16 10:00:00'
select Cast(@.Date as Int) -- Result : 39004

Select @.Date = '2006-10-16 11:59:59'
select Cast(@.Date as Int) -- Result : 39004

Select @.Date = '2006-10-16 12:00:00'
select Cast(@.Date as Int) -- Result : 39005 (expected 39004)

Select @.Date = '2006-10-16 23:00:00'
select Cast(@.Date as Int) -- Result : 39005 (expected 39004)

Select @.Date = '2006-10-17 10:00:00'
select Cast(@.Date as Int) -- Result : 39005

Understood, the Integer cast will take one day from 12:00:00 PM to 11:59:59 AM (Strange Buddy = Bcs, after 12AM the value will be >= 39004.5 when the float number converted to integer it will round off the value...)

To overcome this issue use the following query..

SELECT distinct CAST(Convert(Datetime,Convert(Varchar,[RepTime],101)) AS INT) FROM TableA

Here we are omiting the Time field completly..

|||

Lucky P wrote:

That's because converting to INT don't care of Hours/Minutes/Seconds, just using the Days to separate.

Nope... Time is big concern here Lucky P

|||

You're completely right...

When converting to a Decimal Value, the Date '2006-10-16 12:00:00' is retrieved as 39004.5, which is rounded up to 39005 when converting it to INT.....

I made the mistake because im running the query before 12:00

|||

Yes absolutly it is round off issue... ..

So we can overcome this issue by the following query ..

SELECT distinct Cast(Round(CAST([RepTime] AS Float),0,2) as INT) FROM TableA

|||I got it. I made the same mistake too. Thanks to you all!|||

I use this one now:

SELECT distinct floor(cast([RepTime] as float)) FROM TableA