datetime field format

[colin_nz]

I have an Access 2003 Data Project connected to a SQL Server 2000 back end.

The ADP has a form which has a text box that displays a datetime field which
I use to hold just the time portion of a datetime.

The text box is formatted as Short Time and the contents of the text box look
like this 13:24 until the user clicks in the text box, when it displays this
30/12/1899 1:24:00 p.m.

I understand why the time is stored like this in SQL Server, but it is
disconcerting for the users who want to edit the time to have it change
appearance like that when they click in the text box.

Is there any way of preserving the formatting when the user clicks the text
box?

(When the front end was an Access database with ODBC links to SQL Server
tables, the formatting was preserved when the user clicked the text box).

Thanks

 

[Sylvain Lafontaine]

Replace the date part 30/12/1899 with 01/01/1900 and you should be OK for
the display of the time.

01/01/1900 is the null date on SQL-Server (it is 30/12/1899 for Access) but
ADP should understand that it's the null date even if it's not 30/12/1899.
On some occasions, ADP can even replace 30/12/1899 with 01/01/1900 (or
vice-versa, I don't remember exactly); so you must be careful about that.

 

[colin_nz]

Thanks - that worked like magic.

I am guessing the 30/12/1899's originally got in there because I used the
Access upsizing wizard to move the data from the original Access back end to
SQL Server.

The ADP is currently under development but when it goes live I will need to
migrate the live data from Access to SQL server. Is there a way to do it so
that the 30/12/1899's become 01/01/1900's (perhaps using SQL Server's Data
Transformation Services)?

Replace the date part 30/12/1899 with 01/01/1900 and you should be OK for
the display of the time.

01/01/1900 is the null date on SQL-Server (it is 30/12/1899 for Access) but
ADP should understand that it's the null date even if it's not 30/12/1899.
On some occasions, ADP can even replace 30/12/1899 with 01/01/1900 (or
vice-versa, I don't remember exactly); so you must be careful about that.

I have an Access 2003 Data Project connected to a SQL Server 2000 back end.

[quoted text clipped - 20 lines]

Thanks

 

[Sylvain Lafontaine]

With something like that:

CREATE PROCEDURE dbo.TransformHoursTowardADP
AS
UPDATE t SET t.Hour = Convert (DateTime, Convert (char (10), t.Hour, 108))
From dbo.Table1 as t
WHere t.Hour is not null and Convert (char (10), t.Hour, 102) <>
'1900.01.01'

Not the best way of doing it but will do the job.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Thanks - that worked like magic.

I am guessing the 30/12/1899's originally got in there because I used the
Access upsizing wizard to move the data from the original Access back end
to
SQL Server.

The ADP is currently under development but when it goes live I will need
to
migrate the live data from Access to SQL server. Is there a way to do it
so
that the 30/12/1899's become 01/01/1900's (perhaps using SQL Server's Data
Transformation Services)?

Replace the date part 30/12/1899 with 01/01/1900 and you should be OK for
the display of the time.

01/01/1900 is the null date on SQL-Server (it is 30/12/1899 for Access)
but
ADP should understand that it's the null date even if it's not 30/12/1899.
On some occasions, ADP can even replace 30/12/1899 with 01/01/1900 (or
vice-versa, I don't remember exactly); so you must be careful about that.

I have an Access 2003 Data Project connected to a SQL Server 2000 back
end.

[quoted text clipped - 20 lines]

Thanks

 

[colin_nz via AccessMonster.com]

thanks for your help - I really appreciate it

With something like that:

CREATE PROCEDURE dbo.TransformHoursTowardADP
AS
UPDATE t SET t.Hour = Convert (DateTime, Convert (char (10), t.Hour, 108))
From dbo.Table1 as t
WHere t.Hour is not null and Convert (char (10), t.Hour, 102) <>
'1900.01.01'

Not the best way of doing it but will do the job.

Thanks - that worked like magic.

[quoted text clipped - 25 lines]