datetime format problem

Is there any standard function for inserting datetime values to an sql table. I'm having a problem because some operating systems are in english and some operating systemes are in spanish.. When I insert a value '2005-02-15 12:00:00' it works on the english operating system, but it doesn't in the spanish one... any ideas?try GETDATE ( )|||I am getting the date/time from a date time picker in visual basic.. it's not the current date.|||Define date time picker. Sounds like you need one or more of the following:

A smarter date time picker that converts all datetimes to a standard format.
To write a routine that adds intelligence to your picker.
Some VB function that does b. for you. The lack of strong-typing in VB may make this very difficult.

SQL Server has this interesting trait of trying to do exactly what you ask it to do. You may also want to read up on Cast/Convert in SQL BOL.|||???

If it is a VB control I would think it would be returning a VB datetime value, which is just a number with no formatting applied.

But if you are converting this to a string and submitting it to SQL Server as a datetime value (which you shouldn't be doing), then this format should be universally recognized by SQL Server:

yyyy-mm-dd hh:mi:ss

...where hh uses a 24 hour clock.|||If it is a VB control I would think it would be returning a VB datetime value, which is just a number with no formatting applied.Excellent point. So, diegocro, why is the value being sent a text string?|||Am I missing something (very possible)? How else can you send a date value from VB to SQL?|||Unlikely. You don't miss much.

But the VB code could be converting or storing the data in any number of odd ways before it is submitted to SQL Server. A lot can happen to data at point B while it is traveling from point A to point C...|||Or you could try CAST or CONVERT to change the text string to a DATE format.

e.g. CONVERT(datetime,'2005-02-15 12:00:00' ,120) (see BOL for more detail)|||you can do something like this

convert(datetime,datepickervalue,101)|||[sniped]
[sniped]
[sniped]
[sniped]
[sniped]|||ok
CONVERT(datetime,'2005-02-15 12:00:00' ,120) works, thanks a lot.

datefirst in syslanguages

The accounting calandar for my project requires that weeks start on Saturday, not Sunday (which is the default for English). I want to set datefirst to 6 (Saturday) in syslanguages for English so that I don't have to set it for each date transaction.

In SQL 2000 I hacked it by editing the British language to set datefirst to 6 and dateformat to mdy. Then I set all users of my database to use the British language. This has worked fine for years.

In 2005 I don't know how to edit sys.syslanguages to make these changes and I don't know any other way to globally set the datefirst to 6 (for English preferrably).

I am very frustrated with SQL Server 2005.

Any help would be appreciated.

Thanks,

Sue

Great question. Not sure if there is anything you could do other than setting datefirst for each connection.

I'll post in the private group and see if anyone knows of a way.

Modifying system tables are no longer possible in sql2k5.|||

I appreciate this. Everything about my application revolves around the first day of the week being Saturday, not Sunday. I guess I will look into how much work it will be to start the set command within all calculations, functions, computed columns, etc.

Unfortunately, I don't think I can do a set in a view and this is REALLY gonna hurt.

Any information you can provide would be appreciated.

Sue

|||Look like there isn't anything you can set it globally. Sorry.|||

Wow.

Do you know a way to add a new language that I define? Then I can assign users to this new language.

Thank you.

|||No. It's not possible to do so. The data for syslanguages comes from OpenRowset(TABLE
SYSLANG) which is an internal call to a dll file.|||

Simply amazing......

Ok, for others who may need to do this, I saw a suggestion in another newsgroup for a way to tackle this problem.

Create a table full of the dates for all the days of the years you are interested in and create your own wk reference column.

It looks like I can set datefirst within a stored procedure, but not a function, view, or computed column, so the table solution may be my only option.

The ramifications of this are large for me. If anyone has other suggestions, I'd be glad to hear them.