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 Function, Reporting, Visual Studio 2003

I would like to use a function to truncate the time of day from the date, so
that sorting will be only by date and not date/time of day.
How is this done?I ran into same issue. I have a workaround, but I really am interested to
know the solution. My workaround was to use convert(varchar,datetimefield,
101) - this removes time portion from the field, but problem with this is it
converts into varchar, so the sorting doesn't work as you would need.
Vipul
"Jim" wrote:
> I would like to use a function to truncate the time of day from the date, so
> that sorting will be only by date and not date/time of day.
> How is this done?
>

Date Filter

I have a report in Visual Studio that I want to limit to just the activity for today. Normally I would add a WHERE stmt to the sql script that says docdate=getdate(). However this does not return any data to me in VS. I have tried many different syntax. Does anyone have the correct syntax or filter value that works in Visual Studio? Thank you.

getdate() returns a datetime, not just a date, which is why your sql query is failing. For a really helpful list of date formatting in SQLServer check this url: http://www.sql-server-helper.com/tips/date-formats.aspx

You have two options for selecting your activity records:

- do it purely in sql: use getdate to get an initial date, CONVERT it to get rid of the time component and store it as your start date, use DATEADD on it to add 24 hours and store this as the end date, then use a BETWEEN in the WHERE clause

- pass a date (as a string) through from the report parameters (could be a hidden parameter with a default value of =Format(Now, "yyyy/MM/dd")), then CAST it as a datetime in sql, and compare your activity records to it in the WHERE clause.

Date field cannot be null!

Visual Basic 2005 Professional Edition:

I have an SQL database table that includes a BirthDate field. I would like to have this field as optional when adding a record, but, SQL insists on throwing an exception if the field is null.

With this it looks like your table design has the field set not to allow nulls. You will have to alter the table definition to allow nulls for that field. This can be done in raw tsql, or using the table design views in either of the management studio tools.

|||

I went into DataSetDesigner and in Properties I set AllowDbNull to True.

But, when I tried to change the NullValue property from "Throw Exception" to "Empty" or "Nothing" (there are only 3 choices) it said "For columns not defined as System.String, the only valid value is (Throw exception)".

|||

Given that you've just allowed nulls, the value of the NullValue property is irrelevant, as the exception should never be raised.

|||

The following exception occurred in the DataGridView:

System.Data.NoNullAllowedException: Column 'BirthDate' does not allow nulls

I set 'AllowDbNull' to True in the properties field of the DataSet Designer, replied 'Yes' to 'Save Changes', but, when I go back in, 'AllowDbNull' is back to False.

|||

ok - I've done some research on this (I'm a SQL guy, not a VS guy) and it looks like there may be a bug in the Dataset Designer for non-string columns. I found what looks like a workaround here: http://www.codeproject.com/useritems/Bug_fixed_in_DataDesigner.asp

Hope this helps

Date Datatype

I am using Visual Studio .NET 2003 with SQL Server 2000. I am trying to
insert the date and time into a SQL database by using hour(now). I am having
a hard time trying to figure out which datatype to use in SQL to store this
value. I have tried using datetime, char, nchar, text and nothing seems to
work. Anyone have any ideas? Thanks!
Regards, :)
Christopher BowenDo you want to store the time in terms of hours only?
Madhivanan|||Christopher,
Is the datatype of the column a DateTime field and make sure the variable
storing this value (in .NET i assume) is a System.DateTime - or use
System.DateTime.Now. Using the SqlCommand Object add a parameter of type
datetime and assign this .net variable eg:
public void InsertDateTime()
{
DB Access setup....
...
using (SqlCommand command = new SqlCommand(etc...));
{
command.Connection = someConnectionPreviouslySetup;
command.CommandText = "INSERT INTO TableToInsertInto
(DateTimeField) VALUES (@.DateTimeField)";
command.CommentType = CommandType.Text;
//add the parameter to the parameters collection
command.Parameters.Add("@.DateTimeField", SqlDbType.DateTime);
//set the parameter to the required value
command.Parameters["@.DateTimeField"].Value =
System.DateTime.Now;
command.ExecuteNonQuery();
}
}
I am doing a mini sample in c# (this will NOT compile).
Regards,
Adrian.
<madhivanan2001@.gmail.com> wrote in message
news:1109141955.543915.123420@.g14g2000cwa.googlegroups.com...
> Do you want to store the time in terms of hours only?
> Madhivanan
>|||Hi
Format your value as 'YYYYMMDD' and try to insert
"Christopher Bowen" <c_bowen@.earthlink.net> wrote in message
news:%23OdiaKXGFHA.1740@.TK2MSFTNGP09.phx.gbl...
> I am using Visual Studio .NET 2003 with SQL Server 2000. I am trying to
> insert the date and time into a SQL database by using hour(now). I am
having
> a hard time trying to figure out which datatype to use in SQL to store
this
> value. I have tried using datetime, char, nchar, text and nothing seems to
> work. Anyone have any ideas? Thanks!
> Regards, :)
> Christopher Bowen
>|||I will give your examples a try and I will let you know how it turns out.
Thanks for the help!
"Christopher Bowen" <c_bowen@.earthlink.net> wrote in message
news:#OdiaKXGFHA.1740@.TK2MSFTNGP09.phx.gbl...
> I am using Visual Studio .NET 2003 with SQL Server 2000. I am trying to
> insert the date and time into a SQL database by using hour(now). I am
having
> a hard time trying to figure out which datatype to use in SQL to store
this
> value. I have tried using datetime, char, nchar, text and nothing seems to
> work. Anyone have any ideas? Thanks!
> Regards, :)
> Christopher Bowen
>|||It worked! I went into my table and changed the datatype to "datetime" and
used the code system.datetime.now. Thanks to all of you for all of your
help!
Kind Regards, :)
Christopher Bowen
"Christopher Bowen" <c_bowen@.earthlink.net> wrote in message
news:%23OdiaKXGFHA.1740@.TK2MSFTNGP09.phx.gbl...
>I am using Visual Studio .NET 2003 with SQL Server 2000. I am trying to
>insert the date and time into a SQL database by using hour(now). I am
>having a hard time trying to figure out which datatype to use in SQL to
>store this value. I have tried using datetime, char, nchar, text and
>nothing seems to work. Anyone have any ideas? Thanks!
> Regards, :)
> Christopher Bowen
>