Dates in SQL Server

[Brent]

Hello,

I have an ASP form that has a Date field. The form is used to update data in an SQL Server. The problem I have is that when the user updates the date to say 01/07/2003, the date is saved to the Table as 01/01/1900. If anyone has any ideas, it would be appreciated.

Thanks.

[CJM]

By far the best way to handle dates is to convert them to ISO date format:

20040116 would work, but 2004-01-16 is better (works on Access too).
Times should follow the hh:mm:ss format.

When you pull the dates out again they should be in a suitable format, but
if not you can set Session.LCID to an
appropriate value for you country.

hth

CJM

"Brent" <anonymous@discussions.microsoft.com> wrote in message
news:C6E1CD30-B8FD-468A-B3FD-096519E8548C@microsoft.com...

> Hello,
>
> I have an ASP form that has a Date field. The form is used to update data

in an SQL Server. The problem I have is that when the user updates the date
to say 01/07/2003, the date is saved to the Table as 01/01/1900. If anyone
has any ideas, it would be appreciated.

>
> Thanks.

[Steve G]

For SQL server, as opposed to SQL Server 2000:
From the Microsoft Knowledge Base:

SYMPTOMS
Any local variable of fixed length for which there is an implicit or
explicit conversion to SMALLDATETIME will inadvertently convert NULL to the
default date Jan 1, 1900 12:00AM.

A fixed length binary variable will also convert NULL to the default date
when the IMPLICIT conversion is to DATETIME.

WORKAROUND
To work around this problem, use variables of variable length rather than
fixed length.

STATUS
Microsoft has confirmed this to be a problem in SQL Server versions 4.2x,
6.0, and 6.5. We are researching this problem and will post new information
here in the Microsoft Knowledge Base as it becomes available.

HTH

Steve G

"Brent" <anonymous@discussions.microsoft.com> wrote in message
news:C6E1CD30-B8FD-468A-B3FD-096519E8548C@microsoft.com...

> Hello,
>
> I have an ASP form that has a Date field. The form is used to update data

in an SQL Server. The problem I have is that when the user updates the date
to say 01/07/2003, the date is saved to the Table as 01/01/1900. If anyone
has any ideas, it would be appreciated.

>
> Thanks.

[Aaron Bertrand [MVP]]

> 20040116 would work, but 2004-01-16 is better (works on Access too).

Actually, the latter can break in SQL Server. The two "unbreakable" formats
for SQL Server are:

YYYYMMDD
YYYY-MM-DDTHH:MM:SS[.sss]

Note that the T is literal, e.g. 2004-01-16T11:04:32.444

And of course, Access doesn't accept either of those, and your suggestion is
the only "unbreakable" format I know for Access.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

> Times should follow the hh:mm:ss format.
>
> When you pull the dates out again they should be in a suitable format, but
> if not you can set Session.LCID to an
> appropriate value for you country.
>
> hth
>
> CJM
>
> "Brent" <anonymous@discussions.microsoft.com> wrote in message
> news:C6E1CD30-B8FD-468A-B3FD-096519E8548C@microsoft.com...

> > Hello,
> >
> > I have an ASP form that has a Date field. The form is used to update

data

> in an SQL Server. The problem I have is that when the user updates the

date

> to say 01/07/2003, the date is saved to the Table as 01/01/1900. If

anyone

> has any ideas, it would be appreciated.

> >
> > Thanks.

>
>

[David Morgan]

Are you sure the date field is not "time only"?

"Brent" <anonymous@discussions.microsoft.com> wrote in message
news:C6E1CD30-B8FD-468A-B3FD-096519E8548C@microsoft.com...

> Hello,
>
> I have an ASP form that has a Date field. The form is used to update data

in an SQL Server. The problem I have is that when the user updates the date
to say 01/07/2003, the date is saved to the Table as 01/01/1900. If anyone
has any ideas, it would be appreciated.

>
> Thanks.