Date Time Field Defaults SQL2K 
Author Message
 Date Time Field Defaults SQL2K

SQL Server 2000- working on a database converted from Access 2000

I have a table that has a currentdate field and the needs to be
mm/dd/yy.  In the default field in SQL I put

(convert(char,getdate(),1))  

and that works fine.

However I cannot get the currenttime field to work.  The format needs
to be: hh:mm:ss.

I have read the BOL and some other sources and I have tried every
style I have seen listed.  (I do not want 24 HR time.)

I have it currently set to:

(convert(char,getdate(),8))

But in new records I am getting :  01/01/1900 10:27:27 AM
The time is correct but why am I getting this bogus date value and how
do I get rid of it?

Thanks



Tue, 20 Jul 2004 23:18:19 GMT
 Date Time Field Defaults SQL2K

John,

SQL Server's datetime data type ALWAYS holds a date and a time. There is no
"date only" or "time only" data type in SQL Server.

Your options include:

1)  "Live with" the default date of Jan 1, 1900, and "work around it" as
need be, or

2) Store the information in a char or varchar data type instead of datetime,
and, if necessary, convert it to a datetime data type (with the accompanying
"Jan 1, 1900" default date) when you need to do date arithmetic with the
value.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

Quote:

> SQL Server 2000- working on a database converted from Access 2000

> I have a table that has a currentdate field and the needs to be
> mm/dd/yy.  In the default field in SQL I put

> (convert(char,getdate(),1))

> and that works fine.

> However I cannot get the currenttime field to work.  The format needs
> to be: hh:mm:ss.

> I have read the BOL and some other sources and I have tried every
> style I have seen listed.  (I do not want 24 HR time.)

> I have it currently set to:

> (convert(char,getdate(),8))

> But in new records I am getting :  01/01/1900 10:27:27 AM
> The time is correct but why am I getting this bogus date value and how
> do I get rid of it?

> Thanks



Thu, 22 Jul 2004 02:48:58 GMT
 Date Time Field Defaults SQL2K
[posted and mailed, please reply in news]

Quote:
 (John) writes:
> SQL Server 2000- working on a database converted from Access 2000

> I have a table that has a currentdate field and the needs to be
> mm/dd/yy.  In the default field in SQL I put

> (convert(char,getdate(),1))  

> and that works fine.

To add to what BP said: a datetime column in SQL Server does not have
any human-readable format at all. It's an 8-bit binary value. When you
access it, you can use CONVERT to get in the format you prefer.

However, I would recommend you to always format dates in the client.
This permits you to use the regional settings of the client. I would
say that this is particulary important if you want an odd date format in
which the fields is in neither YMD or DMY order.

And in such case, there may not be any point of having separate columns
for current time and current date, but have it all in one column, and
then pick the parts apart as needed.

--
Erland Sommarskog, Abaris AB

SQL Server MVP



Thu, 22 Jul 2004 06:22:49 GMT
 Date Time Field Defaults SQL2K

Quote:
>And in such case, there may not be any point of having separate columns
>for current time and current date, but have it all in one column, and
>then pick the parts apart as needed.

Absolutely correct.  I don't know why I had tunnel vision on this.  I
guess just because I was trying to get this Access converted database
up and running ASAP with a minimal of changes.  However, what both of
you have said in your replys makes perfect sense and I thank you for
your direction.

John



Fri, 23 Jul 2004 20:55:18 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Replacing Default TDBEdit Error Handling For Date or Time Fields

2. CREATE TABLE: Now() as default value for a date/time field

3. Current date and time as the Default Value for a DateTime Field

4. ADO with Access Date/Time field not storing the time, just the date

5. how to change dates or times in a oracle date time field

6. Query datetime-field by date when field contains date + time

7. Can't read time from Access 2000 date/time field

8. How to retrieve only the Date from date time field

9. Get rid of date in date/time field

10. Null date values in Date/Time fields in Access DB

11. Extract Date only from Date/Time Field

12. Comparing datetime field (containing date, time values) to a date only string


 
Powered by phpBB® Forum Software