Dates again........ - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Dates again........

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-10-03 : 06:51:28

Not sure if this is a SQL issue or VB.Net

I have a SQL 2008 database. In my table I have a datetime field. I am in the UK and today's date is 3rd October 2011
When I add a record to my database from my PC it is entered in the table as

2011-10-03 11:22:00.000

On another PC in the same organisation it is entered as

2011-03-10 09:50:00.000

This PC treats this date as 10th March - so does my PC

If the date is not reversible (eg 3rd Oct is valid, 10th March is valid) my PC still records the correct date eg

2011-09-29 10:15:00.000

The other PC throws up an error. Is there a way around this ? I am adding the record with a stored procedure.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2011-10-03 : 06:56:35

For desktop application, you must check your client default date settings.

--------------------------
http://connectsql.com/

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-10-03 : 07:08:04

Yes, thank you, this is what I thought.Under control panel regional and language settings, advanced tab I have English(United Kingom) If I go this setting and I have asked our tech support to check, but they say it is correct. I DO NOT BELIEVE THEM !!! Is there any way I can compensate and convert her date to UK before it is saved ?

Kristen
Test

22859 Posts

Posted - 2011-10-03 : 07:24:44

Get your application to provide dates to SQL in the string format 'yyyymmdd' - note that there are NO hyphens. This will be treated unambiguously by SQL Server. Anything which is 99-99-99 or 99-99-9999 is open to SQL Server interpreting as best it can - which depends on lots of factors, and may change (e.g. if the user's connection to SQL changes its Language setting)

If the application passes dates to SQL using "native date variable" in the Application's language that should be fine too.

If you need the time then use either

yyyymmdd hh:mm:ss.sss
or
yyyy-mm-ddThh:mm:ss.sss

the format must be followed exactly in order to be treated unambiguously - i.e. no hyphens in the first one, no spaces in the second one.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-10-03 : 07:36:17

If I use this I get the error 'Error converting data type nvarchar to datetime.' when the sp is excuted. The date is shown on the form as 03/10/2011 12:32

theSQLCommand.Parameters.AddWithValue("@DatePurchased", Format(Me.txtNewDate.Text, "yyyymmdd hh:mm:ss.sss"))

Kristen
Test

22859 Posts

Posted - 2011-10-03 : 09:44:52

Is "Me.txtNewDate" a native date / datetime datatype variable in VB.Net? If so you should be able to just pass it as-is (provided that the @DatePurchased parameters is also a DATE or DATETIME datatype (in SQL)

The error message might be from within the body of the SQL that you are calling, so the parameter might be just-fine.

You could use SQL Profiler to check that - or modify your SQL code to "record" the parameter or somesuch, so that you can investigate it separately.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-10-03 : 09:54:03

Thanks for your reply. Me.txtNewDate is masked text box on my windows form. It displays the date as 03/10/2011 15:35:00 @DatePurchased is type datetime in my sp

I have no problem on my PC, just this other one, which is the only PC that will be running the app. Is there a way I can convert Me.txtNewDate.Text to format 2011-10-03 11:22:00.000 and pass it to the sp regardless of what locale the app is running on ?

Kristen
Test

22859 Posts

Posted - 2011-10-03 : 12:48:18

You could set up the SQL end to have @DatePurchased as a VARCHAR parameter, and then you could EXplicitly cast the VARCHAR to a DATETIME - this will allow you to specify that the VARCHAR is in "yyyy-mm-dd hh:mm:ss.sss" format.

The command is


SELECT @MyDateTimeVariable = CONVERT(datetime, @DatePurchased, 9999)

you'll have to look up the correct value for "9999" in the documentation; that is the "hint" that specifies what style of date @DatePurchased contains.

But you will then have to ensure that the date is only ever passed from the application in that format - and from the sounds of it it depends on the settings on the PC - hence I think it would be better to make the application send a consistent date.

Unfortunately I know nothing about DotNet so I can't advise what you need to do at that end.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources