error converting data type nvarchar to datetime

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

error converting data type nvarchar to datetime

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-08-17 : 09:44:13

I have a windows app which runs fine on my PC but when a user on another PC uses it (and runs an SP to add a record to the dbase) they get this error

error converting data type nvarchar to datetime

I've checked the date format on their PC and it is the same as mine dd/mm/yyyy

Any ideas ? Here's how I'm passing the parameters and in the table
DatePurchased is DateTime and TimePurchased is Time(7)

 theSQLCommand.Parameters.AddWithValue("@DatePurchased", Me.txtNewDate.Text)
        theSQLCommand.Parameters.AddWithValue("@TimePurchased", Me.txtNewTime.Text)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-17 : 09:49:13

is the language setings same on both systems?

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

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-08-17 : 10:08:32

If you mean Britich UK then yes

Kristen
Test

22859 Posts

Posted - 2011-08-17 : 16:09:43

If you want to pass a date / datetime value from application either use a native date / datetime object in your application (so that the language itself can make a safe, implicit, type-conversion), or pass the text-string in the format "yyyymmdd" (for date-only) or "yyyymmdd hh:mm:ss.sss" [NOTE: NO hyphens] or "yyyy-mm-ddThh:mm:ss.sss"[NOTE: the hyphens! and the 'T']. No other string-text format is safe.

These formats are unambiguous to SQL Server and will work regardless of the date/locale settings of both the Client PC and SQL Server's server - and will therefore survive moving the application to different PCs with different client-date settings, and also moving to a different server setting - and also things like a user connection changing Language directive to SQL Server (which, all by itself, will wreck your day using assumed-implicit string-to-date conversion I'm afraid)

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-08-18 : 03:39:11

Thanks for your reply Kirsten, but could you sho wme how to do what you suggest please ?

This is where I pass the paramaters to my sp so how do I pass the text-string in the format yyyy-mm-ddThh:mm:ss.sss

theSQLCommand.Parameters.AddWithValue("@DatePurchased", Me.txtNewDate.Text)
theSQLCommand.Parameters.AddWithValue("@TimePurchased", Me.txtNewTime.Text)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-18 : 04:11:28

use Format() function to change date to yyyymmdd format

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

Kristen
Test

22859 Posts

Posted - 2011-08-18 : 08:15:22

If Me.txtNewDate.Text is in the style "dd/mm/yyyy" you need to use string-manipulation to change it to be in "yyyymmdd"

I would create a function specifically to do that, and use it whenever I was passing a "string date" to SQL

You need to be 100% sure that your String dates are ALWAYS "dd/mm/yyyy" - i.e. the user's locale / PC settings can NOT cause the style to change to, say, mm/dd/yyyy

If you could use a native date object in your application language the problem should go away because it won't have any inherent, user/locale-specific format, it will just be a "date object"

I am not familiar with the application language you are using, so Visaskh's suggestion of the Format() function may be your best bet.

I would do something like:


Define Function fnTextDateToSQL(strDate)
' strDate format must be dd/mm/yyyy
fnTextDateToSQL = MID(strDate, 7, 4) & MID(strDate, 4, 2) & MID(strDate, 1, 2)
End Function

theSQLCommand.Parameters.AddWithValue("@DatePurchased", fnTextDateToSQL(Me.txtNewDate.Text))

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-08-18 : 08:41:30

It's in this format as it'd generated automatically like this when a new record is created

Me.txtNewDate.Text = DateTime.Now

18/08/2011 13:34

curtis.sujata
Starting Member

3 Posts

Posted - 2011-08-18 : 09:22:41

string time=Me.txtNewDate.Text+ " "+ Me.txtNewTime.Text;
DateTime dt = new DateTime();
if(DateTime.TryParse(time,dt)
{
theSQLCommand.Parameters.AddWithValue("@DatePurchased",dt);
}

sujata curtis

Kristen
Test

22859 Posts

Posted - 2011-08-18 : 09:51:56

quote:
Originally posted by Pinto

It's in this format as it'd generated automatically like this when a new record is created

Me.txtNewDate.Text = DateTime.Now

18/08/2011 13:34

If you do it like that, and the settings change on the server that application is running on (e.g. the application gets moved to a shiny new server, but configured differently), then I the format of the date in Me.txtNewDate.Text is liable to change - which will ruin your day ...

Either put the DateTime.Now into your Me.txtNewDate.Text object explicitly formatted to yyyymmdd - which will work unambiguously, or use a DateTime object as sujata curtis has kindly described.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources