Date Issue

Hi,
I have two development PC's. Each PC has SQL Server 2000 and Visual Studio
2005 installed. One has Microsoft Office 2003 installed.
Both have exactly the same regional settings.
When I run a procedure in VB.Net 2005 to store date information in one
database the date is stored mm/dd/yyyy. In the other it is stored as
dd/mm/yyyy.
I want to store the date as dd/mm/yyyy.
I have tried to format the date within the SQL insert - when I debug the
format is dd/mm/yyyy but once the record is saved it reverts to mm/dd/yyyy.
I cannot find any option in SQL Server to format dates.
Any ideas?
Thanks
Darkman
When you store dates use YYYYMMDD , and if you want to display dates on the
client , use formating on the client
http://www.karaszi.com/SQLServer/info_datetime.asp
"Darkman" <Darkman@.discussions.microsoft.com> wrote in message
news:38B34B6C-C90C-49AD-BDE2-7C30E7624FB7@.microsoft.com...
> Hi,
> I have two development PC's. Each PC has SQL Server 2000 and Visual Studio
> 2005 installed. One has Microsoft Office 2003 installed.
> Both have exactly the same regional settings.
> When I run a procedure in VB.Net 2005 to store date information in one
> database the date is stored mm/dd/yyyy. In the other it is stored as
> dd/mm/yyyy.
> I want to store the date as dd/mm/yyyy.
> I have tried to format the date within the SQL insert - when I debug the
> format is dd/mm/yyyy but once the record is saved it reverts to
> mm/dd/yyyy.
> I cannot find any option in SQL Server to format dates.
> Any ideas?
> Thanks
>
|||Two things:
1) I also store the time. So the stored value is:
dd/MM/yyyy HH:mm
2) I would still like to know why the two different installations have
different date formats.
"Uri Dimant" wrote:

> Darkman
> When you store dates use YYYYMMDD , and if you want to display dates on the
> client , use formating on the client
> http://www.karaszi.com/SQLServer/info_datetime.asp
>
>
> "Darkman" <Darkman@.discussions.microsoft.com> wrote in message
> news:38B34B6C-C90C-49AD-BDE2-7C30E7624FB7@.microsoft.com...
>
>
|||Darkman
Why? I do not know , but the matter is to use YYYYMMDD HH:MM format to
store dates
SELECT CAST('20080101 10:23' AS DATETIME)
"Darkman" <Darkman@.discussions.microsoft.com> wrote in message
news:470D14C7-893D-49E7-9B2B-A26C69E11FC1@.microsoft.com...[vbcol=seagreen]
> Two things:
> 1) I also store the time. So the stored value is:
> dd/MM/yyyy HH:mm
> 2) I would still like to know why the two different installations have
> different date formats.
>
> "Uri Dimant" wrote:
|||> I have tried to format the date within the SQL insert - when I debug the
> format is dd/mm/yyyy but once the record is saved it reverts to
> mm/dd/yyyy.
What does DBCC USEROPTIONS return? Different DATEFORMAT settings on the 2
machines would explain why identical datetime strings are interpreted
differently.
In any case, I strongly suggest you use parameterized SQL Statements.
Parameterized statements are more secure, do not require you to format date
strings or double-up quotes and also promote query plan reuse. Example
below.
'create parameterized command
Dim insertCommand AS SqlCommand = _
New SqlCommand( _
"INSERT INTO dbo.MyTable (MyDateTime) " + _
"VALUES(@.MyDateTime)", connection)
'create parameter and set value
insertCommand.Parameters.Add( _
"@.MyDateTime", _
SqlDbType.DateTime).Value = DateTime.Now
'execute command
insertCommand.ExecuteNonQuery()
Hope this helps.
Dan Guzman
SQL Server MVP
"Darkman" <Darkman@.discussions.microsoft.com> wrote in message
news:38B34B6C-C90C-49AD-BDE2-7C30E7624FB7@.microsoft.com...
> Hi,
> I have two development PC's. Each PC has SQL Server 2000 and Visual Studio
> 2005 installed. One has Microsoft Office 2003 installed.
> Both have exactly the same regional settings.
> When I run a procedure in VB.Net 2005 to store date information in one
> database the date is stored mm/dd/yyyy. In the other it is stored as
> dd/mm/yyyy.
> I want to store the date as dd/mm/yyyy.
> I have tried to format the date within the SQL insert - when I debug the
> format is dd/mm/yyyy but once the record is saved it reverts to
> mm/dd/yyyy.
> I cannot find any option in SQL Server to format dates.
> Any ideas?
> Thanks
>
|||If it is a datetime column then it is not "stored" that way at all. It is
how the client tools are showing the dates (or perhaps how the server
interprets the literals that are being passed to it). This can be affected
by regional settings on the client or server, dateformat or language
settings in SQL Server, etc.
On each server, try storing a date like 2007-11-17. If it is because of the
way the date literal is being interpreted, one server should return an
error.
You should never pass strings manually formatted as d/m/y or m/d/y. Always
use ISO formats like YYYYMMDD or YYYY-MM-DDTHH:MM:SS. These are the only
two formats that are guaranteed "safe" in SQL Server. Anything else can be
wrongly interpreted due to several variables, including those listed above.
It is easy to demonstrate this, both using m/d/y and d/m/y literal formats:
SET LANGUAGE BRITISH
SELECT CONVERT(DATETIME, '11/17/2007')
GO
SET LANGUAGE FRENCH
SELECT CONVERT(DATETIME, '11/17/2007')
GO
SET DATEFORMAT DMY
SELECT CONVERT(DATETIME, '11/17/2007')
GO
SET LANGUAGE ENGLISH
SELECT CONVERT(DATETIME, '17/11/2007')
GO
Please read up on Tibor's article:
http://www.karaszi.com/SQLServer/info_datetime.asp
It should reinforce why you should always use the above described formats
when passing date literals. Or, better yet, use parameterized statements
and strongly typed variables when you can, as Dan suggested.
"Darkman" <Darkman@.discussions.microsoft.com> wrote in message
news:38B34B6C-C90C-49AD-BDE2-7C30E7624FB7@.microsoft.com...
> Hi,
> I have two development PC's. Each PC has SQL Server 2000 and Visual Studio
> 2005 installed. One has Microsoft Office 2003 installed.
> Both have exactly the same regional settings.
> When I run a procedure in VB.Net 2005 to store date information in one
> database the date is stored mm/dd/yyyy. In the other it is stored as
> dd/mm/yyyy.
> I want to store the date as dd/mm/yyyy.
> I have tried to format the date within the SQL insert - when I debug the
> format is dd/mm/yyyy but once the record is saved it reverts to
> mm/dd/yyyy.
> I cannot find any option in SQL Server to format dates.
> Any ideas?
> Thanks
>

Date Issue

Hi,
I have two development PC's. Each PC has SQL Server 2000 and Visual Studio
2005 installed. One has Microsoft Office 2003 installed.
Both have exactly the same regional settings.
When I run a procedure in VB.Net 2005 to store date information in one
database the date is stored mm/dd/yyyy. In the other it is stored as
dd/mm/yyyy.
I want to store the date as dd/mm/yyyy.
I have tried to format the date within the SQL insert - when I debug the
format is dd/mm/yyyy but once the record is saved it reverts to mm/dd/yyyy.
I cannot find any option in SQL Server to format dates.
Any ideas?
ThanksDarkman
When you store dates use YYYYMMDD , and if you want to display dates on the
client , use formating on the client
http://www.karaszi.com/SQLServer/info_datetime.asp
"Darkman" <Darkman@.discussions.microsoft.com> wrote in message
news:38B34B6C-C90C-49AD-BDE2-7C30E7624FB7@.microsoft.com...
> Hi,
> I have two development PC's. Each PC has SQL Server 2000 and Visual Studio
> 2005 installed. One has Microsoft Office 2003 installed.
> Both have exactly the same regional settings.
> When I run a procedure in VB.Net 2005 to store date information in one
> database the date is stored mm/dd/yyyy. In the other it is stored as
> dd/mm/yyyy.
> I want to store the date as dd/mm/yyyy.
> I have tried to format the date within the SQL insert - when I debug the
> format is dd/mm/yyyy but once the record is saved it reverts to
> mm/dd/yyyy.
> I cannot find any option in SQL Server to format dates.
> Any ideas?
> Thanks
>|||Two things:
1) I also store the time. So the stored value is:
dd/MM/yyyy HH:mm
2) I would still like to know why the two different installations have
different date formats.
"Uri Dimant" wrote:
> Darkman
> When you store dates use YYYYMMDD , and if you want to display dates on the
> client , use formating on the client
> http://www.karaszi.com/SQLServer/info_datetime.asp
>
>
> "Darkman" <Darkman@.discussions.microsoft.com> wrote in message
> news:38B34B6C-C90C-49AD-BDE2-7C30E7624FB7@.microsoft.com...
> > Hi,
> >
> > I have two development PC's. Each PC has SQL Server 2000 and Visual Studio
> > 2005 installed. One has Microsoft Office 2003 installed.
> >
> > Both have exactly the same regional settings.
> >
> > When I run a procedure in VB.Net 2005 to store date information in one
> > database the date is stored mm/dd/yyyy. In the other it is stored as
> > dd/mm/yyyy.
> >
> > I want to store the date as dd/mm/yyyy.
> >
> > I have tried to format the date within the SQL insert - when I debug the
> > format is dd/mm/yyyy but once the record is saved it reverts to
> > mm/dd/yyyy.
> >
> > I cannot find any option in SQL Server to format dates.
> >
> > Any ideas?
> >
> > Thanks
> >
>
>|||Darkman
Why? I do not know , but the matter is to use YYYYMMDD HH:MM format to
store dates
SELECT CAST('20080101 10:23' AS DATETIME)
"Darkman" <Darkman@.discussions.microsoft.com> wrote in message
news:470D14C7-893D-49E7-9B2B-A26C69E11FC1@.microsoft.com...
> Two things:
> 1) I also store the time. So the stored value is:
> dd/MM/yyyy HH:mm
> 2) I would still like to know why the two different installations have
> different date formats.
>
> "Uri Dimant" wrote:
>> Darkman
>> When you store dates use YYYYMMDD , and if you want to display dates on
>> the
>> client , use formating on the client
>> http://www.karaszi.com/SQLServer/info_datetime.asp
>>
>>
>> "Darkman" <Darkman@.discussions.microsoft.com> wrote in message
>> news:38B34B6C-C90C-49AD-BDE2-7C30E7624FB7@.microsoft.com...
>> > Hi,
>> >
>> > I have two development PC's. Each PC has SQL Server 2000 and Visual
>> > Studio
>> > 2005 installed. One has Microsoft Office 2003 installed.
>> >
>> > Both have exactly the same regional settings.
>> >
>> > When I run a procedure in VB.Net 2005 to store date information in one
>> > database the date is stored mm/dd/yyyy. In the other it is stored as
>> > dd/mm/yyyy.
>> >
>> > I want to store the date as dd/mm/yyyy.
>> >
>> > I have tried to format the date within the SQL insert - when I debug
>> > the
>> > format is dd/mm/yyyy but once the record is saved it reverts to
>> > mm/dd/yyyy.
>> >
>> > I cannot find any option in SQL Server to format dates.
>> >
>> > Any ideas?
>> >
>> > Thanks
>> >
>>|||> I have tried to format the date within the SQL insert - when I debug the
> format is dd/mm/yyyy but once the record is saved it reverts to
> mm/dd/yyyy.
What does DBCC USEROPTIONS return? Different DATEFORMAT settings on the 2
machines would explain why identical datetime strings are interpreted
differently.
In any case, I strongly suggest you use parameterized SQL Statements.
Parameterized statements are more secure, do not require you to format date
strings or double-up quotes and also promote query plan reuse. Example
below.
'create parameterized command
Dim insertCommand AS SqlCommand = _
New SqlCommand( _
"INSERT INTO dbo.MyTable (MyDateTime) " + _
"VALUES(@.MyDateTime)", connection)
'create parameter and set value
insertCommand.Parameters.Add( _
"@.MyDateTime", _
SqlDbType.DateTime).Value = DateTime.Now
'execute command
insertCommand.ExecuteNonQuery()
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Darkman" <Darkman@.discussions.microsoft.com> wrote in message
news:38B34B6C-C90C-49AD-BDE2-7C30E7624FB7@.microsoft.com...
> Hi,
> I have two development PC's. Each PC has SQL Server 2000 and Visual Studio
> 2005 installed. One has Microsoft Office 2003 installed.
> Both have exactly the same regional settings.
> When I run a procedure in VB.Net 2005 to store date information in one
> database the date is stored mm/dd/yyyy. In the other it is stored as
> dd/mm/yyyy.
> I want to store the date as dd/mm/yyyy.
> I have tried to format the date within the SQL insert - when I debug the
> format is dd/mm/yyyy but once the record is saved it reverts to
> mm/dd/yyyy.
> I cannot find any option in SQL Server to format dates.
> Any ideas?
> Thanks
>|||If it is a datetime column then it is not "stored" that way at all. It is
how the client tools are showing the dates (or perhaps how the server
interprets the literals that are being passed to it). This can be affected
by regional settings on the client or server, dateformat or language
settings in SQL Server, etc.
On each server, try storing a date like 2007-11-17. If it is because of the
way the date literal is being interpreted, one server should return an
error.
You should never pass strings manually formatted as d/m/y or m/d/y. Always
use ISO formats like YYYYMMDD or YYYY-MM-DDTHH:MM:SS. These are the only
two formats that are guaranteed "safe" in SQL Server. Anything else can be
wrongly interpreted due to several variables, including those listed above.
It is easy to demonstrate this, both using m/d/y and d/m/y literal formats:
SET LANGUAGE BRITISH
SELECT CONVERT(DATETIME, '11/17/2007')
GO
SET LANGUAGE FRENCH
SELECT CONVERT(DATETIME, '11/17/2007')
GO
SET DATEFORMAT DMY
SELECT CONVERT(DATETIME, '11/17/2007')
GO
SET LANGUAGE ENGLISH
SELECT CONVERT(DATETIME, '17/11/2007')
GO
Please read up on Tibor's article:
http://www.karaszi.com/SQLServer/info_datetime.asp
It should reinforce why you should always use the above described formats
when passing date literals. Or, better yet, use parameterized statements
and strongly typed variables when you can, as Dan suggested.
"Darkman" <Darkman@.discussions.microsoft.com> wrote in message
news:38B34B6C-C90C-49AD-BDE2-7C30E7624FB7@.microsoft.com...
> Hi,
> I have two development PC's. Each PC has SQL Server 2000 and Visual Studio
> 2005 installed. One has Microsoft Office 2003 installed.
> Both have exactly the same regional settings.
> When I run a procedure in VB.Net 2005 to store date information in one
> database the date is stored mm/dd/yyyy. In the other it is stored as
> dd/mm/yyyy.
> I want to store the date as dd/mm/yyyy.
> I have tried to format the date within the SQL insert - when I debug the
> format is dd/mm/yyyy but once the record is saved it reverts to
> mm/dd/yyyy.
> I cannot find any option in SQL Server to format dates.
> Any ideas?
> Thanks
>

Date format US/UK

Hi

I have installed SQL server 2005 developer edition.When trying to insert a date in the UK format '16/04/2007' I get the following error...

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value

if I change the date to be inserted to an American format then I am able to insert it.

I don't want to change the sql as this is something that has always worked - but not with my local install of sql server.

other info that may be of relevance...

My regional settings are all set to UK and this has been applied to the default profile
The server & database collation is Latin1_General_CI_AS
If I attach this database to another instance of sql server installed on a another machine then the insert works.
My pc is a Dell XP Professional SP2

any/all help is appreciated!

chris

Before your insert query add the following statement

SET DATEFORMAT dmy

Insert .....|||

Hi

Thanks for the post

I am thinking maybe I have posted in the wrong forum?

what I don't want to do is write more TSQL, rather I would like to know

what is causing the date to be inserted in the US format so that I can change the setting.

thanks

chris

|||Yes you are absolutly correct.. |||

Rather than doing an implicit conversion by inserting the text into a datetime field use an explicit conversion (onto which you can place a style identifier.

Rather than '16/04/2007' Use CONVERT(datetime, '16/04/2007', 103)|||

Thanks for the posts

I have posted this in the database engine forum so please dont post anymore to this thread.

regards

chris

Date format UK/US

Hi

I have installed SQL server 2005 developer edition.When trying to insert a date in the UK format '16/04/2007' I get the following error...

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value

if I change the date to be inserted to an American format then I am able to insert it.

I don't want to change the sql as this is something that has always worked - but not with my local install of sql server.

other info that may be of relevance...

My regional settings are all set to UK and this has been applied to the default profile
The server & database collation is Latin1_General_CI_AS
If I attach this database to another instance of sql server installed on a another machine then the insert works.
My pc is a Dell XP Professional SP2

What I don't want to do is write more TSQL but change settings that can alter the behaviour.

any/all help is appreciated!

chris

Chris,

See your other post in Transact-SQL for responses.

Often, the quality of the responses received is related to our ability to ‘bounce’ ideas off of each other. In the future, to make it easier for us to offer you assistance, and to prevent folks from wasting time on already answered questions, please don't post to multiple newsgroups. Choose the one that best fits your question and post there. Only post to another newsgroup if you get no answer in a day or two (or if you accidentally posted to the wrong newsgroup –and you indicate that you've already posted elsewhere).

|||

Thanks for your reply

If you read my other post I realise that I have posted in the wrong forum and I clearly state that! as I do not want a TSQL solution.

hence I posted here afterwards

but thank you for your help

|||

Oh and this has not been answered so all constructive help is still appreciated

thanks

chris

|||

Ok one of my colleagues has fixed it.

It turns out that us guys in England don't speak English - we speak British English?

The default language on the Login had to be set to British English.

thanks

chris

Date Format Display Convert US to International

Hi,
I am working on a Report that needs to display dates in International Format
(UK) dd/mm/yy, the database and Sql RS are installed on a US date format
mm/dd/yy, how do I get the date to display correctly in the Report?
Cheers
Bobselect the textbox and properties and go to format and select custom and
give your format e.g dd-mm-yyyy
Amarnath
"Bob" wrote:
> Hi,
> I am working on a Report that needs to display dates in International Format
> (UK) dd/mm/yy, the database and Sql RS are installed on a US date format
> mm/dd/yy, how do I get the date to display correctly in the Report?
> Cheers
> Bob
>|||Amarnath,
Thanks, had tried that. Found you need to change Language in
Properties/International/Launguage to English (United
Kingdom).............
Bob
"Amarnath" <Amarnath@.discussions.microsoft.com> wrote in message
news:D6405D61-4847-43D3-AD6E-E7C396BCE7DC@.microsoft.com...
> select the textbox and properties and go to format and select custom and
> give your format e.g dd-mm-yyyy
> Amarnath
> "Bob" wrote:
>> Hi,
>> I am working on a Report that needs to display dates in International
>> Format
>> (UK) dd/mm/yy, the database and Sql RS are installed on a US date format
>> mm/dd/yy, how do I get the date to display correctly in the Report?
>> Cheers
>> Bob

Date between 1/1/0001 to 31/12/9999 possible in Sql server

I installed SQL Server Express and SQL Server manager, then tried to start manager. SQL Service is running, but SQL Manager attempt to connect to SQL Server instance times-out even though I correctly identify instance as SQLEXPRESS? Everything seems to be normal - both SQL Server and Manager are on a single Windows XP home machine. Any suggestions. ThanksHi,
Try using .\SQLEXPRESS as the instance name...

cheers,
Paul June A. Domag|||Great - It Works! Thanks|||After reading this thread and another one I've tried entering the following in express manager and I still can't connect:

.\MSSQLSERVER
(local)\MSSQLSERVER

I am running SQL Server Express June CTP on a Win2K3 box. SQL Express Manager is also running on the same Win2K3 box.

I have opened all the network protocols for SQL Server Express, I've also enabled the SQL Server browser. There are no firewalls involved. This is probably the most insecure setup, yet I still cannot connect locally

Any ideas?

I followed all the instructions here:
http://download.microsoft.com/download/a/6/3/a63ec922-913e-4228-93e3-970abff3fd66/ReadmeSQLExpMgr.htm
and here
http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

Nothing seems to work.
|||Hi,
Actually its .\SQLServer not .\MSSQLSERVER.
Have you tried it? got any versions installed before the June CTP?
cheers,
Paul June A. Domag|||Unfortunately that didn't work either.
All I have installed is June CTP on a fresh install of Win2K3 Standard.
In the SQL Server Configuration Manager under "name" it says: SQL Server (MSSQLSERVER).
This would indicate the name of the SQL server is "MSSQLSERVER" correct? I chose "default" instance during the install and that is the name it gave itself.
This is getting frustrating... I thought programming with MS stuff would be easy. I could have connected to MySQL a hundred different ways by now.
|||Just a two cents worth general comment - the MS beta's tend to have problems that the production versions don't - having used SQL Server from the time it was Sybase Server to date I can say it is great software that occassionally suffers from growing pains. PS|||Yeah, I don't expect Beta software to be 100% functional but I do expect the basic functionality to be working and straightforward. Connecting to the server shouldn't be this difficult. Especially if you consider the target market for this product.
|||Hi,
Oops, I guess I had a typo. Its .\SqlExpress, If you choose default instance...
cheers,
Paul June A. Domag|||After all that I accidently stumbled upon a solution.
The only thing that works for my installation is just plain ".\" (no quotes) and nothing else. Go figure. Hopefully this post saves someone else a lot of time.
|||Hi,
Weird... Could you report a bug in Product Feedback? So that Microsoft would be aware of this...

cheers,
Paul June A. Domag|||thanks a lot this helped!!!!!

Paul Domag wrote:

Hi,
Try using .\SQLEXPRESS as the instance name...

cheers,
Paul June A. Domag

|||Thanks!!!! I have been pulling my hair out all night trying to figure out why I couldn't connect. That did the trick. Why doesn't MS tell you about these important details.|||

I'm getting error like

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

I want to set it bet 1/1/0001 to 31/12/9999. how to do this?

So do u have any solution over it pls.

I think u have solution over it because I have seen same problem u had .

At last Happy New Year 2006 at this first meet ing with u

Balasaheb

|||Hi,

There are only two datatypes that could handle date and time values in sqlserver. These are DateTime and SmallDateTime. Both of these types cold not handle data between1/1/0001 to 31/12/9999. But you can store these dates as a varchar and just parse the dates to calculate them in your program...

cheers,

Paul June A. Domag