We help IT Professionals succeed at work.

Insert Time but NOT Date into datetime field, using SQL in ASP

niico
niico asked
on
Medium Priority
489 Views
Last Modified: 2007-12-19
I am using SQL Server 2000, ASP, IIS 5

I want to insert a time (taken from a form) into a datetime field, but not a date. If I do this manually through Enterprise Manager its fine, though when I attempt this with SQL in an ASP page it puts 01/01/1900 in as a date.

How can I execute this SQL statement without inserting a date at all?
Comment
Watch Question

Commented:
listening..
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
Actually, a datetime field has ALWAYS a date and a time value. It's only a question of displaying the values.
If you don't give a date, the base date of 1/1/1900 is used instead, and even if EM doesn't display that part, it is stored as such.

CHeers

Author

Commented:
Why would it display it for the SQL entered fields and not in the fields I type in through EM? Is it entering a NULL value in date or somthing? It's doing something different thats for sure..
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
the datetime field is a single value, so there is nothing like a NULL for the datetime.
Now, the difference from inserting the "time only" from EM and ASP is the values used. ASP (probably using java or vbscript + ADO) will put itself a date value, while EM doesn't...
CHeers

Author

Commented:
ok how do i emulate what EM does in SQL/ASP - any way?

Commented:
I don't have access to VbScript on this machine but ...
Can you use the Time function.  
insert into myTable(timecolumn)values(time(time_field.txt))
Commented:
Skip the time() function idea.
When I try to enter a time using EM (like 12:30), then it's true that all I see in EM is 12:30:00 PM. But when I do a query on that table I get a date 1899-12-30. So I think you are going to get a date regardless.
Try this in EM, give it a date and time but use 12/30/1899 as the date. You will still see only the time in EM. This is a bug/feature in EM. Note that when you convert a datetime value with year < 1900 to a float, you get a negative number. Also note that 1/1/1900 is the low value for smalldatetime. I suspect this has something to do with EM's behavior in displaying datetime values.

Author

Commented:
phew..thankx

ok so im the only person that has ever needed to put just a time in a datetime field  - you can do it with date so why not time?!>surely i cant be alone?

Commented:
Skip the time() function idea.
When I try to enter a time using EM (like 12:30), then it's true that all I see in EM is 12:30:00 PM. But when I do a query on that table I get a date 1899-12-30. So I think you are going to get a date regardless.
Try this in EM, give it a date and time but use 12/30/1899 as the date. You will still see only the time in EM. This is a bug/feature in EM. Note that when you convert a datetime value with year < 1900 to a float, you get a negative number. Also note that 1/1/1900 is the low value for smalldatetime. I suspect this has something to do with EM's behavior in displaying datetime values.

Commented:
Sorry for the double post. Actually you can't enter just the date either. The time will get set to midnight.
I think I have used some time-only fields and probably did one of the following:
1. Use a text string. A nuisance if you want to arithmetic.
2. Use datetime and just ignore the date part.
3. Use a style format (see CONVERT) of 8 or 14 that shows time only.
4. Convert the datetime field to a string and hack off the date part using substring.
5. Pull the time parts out of a datetime field use DATEPART.

Author

Commented:
When I enter the date only through ASP then view the table in EM or pulled out of the DB or linked via ODBC into access I see only the date?!

Author

Commented:
The CONVERT thing looks interesting - how could I use that/what is it?

Commented:
What I have always done in the past is store the time in a datetime field, but when my stored procedure returned the information it would do something like...
SELECT
CONVERT(VARCHAR(50),l.call_strt_tm,108) AS 'call_strt_tm'
FROM SomeTable

(this is point 3 that pkohlmil mentioned)

Author

Commented:
It must be possible to store this without a date though - if i put in just a time in enterprise manager then get the contents of that field and spit it out into an asp page it comes up with, just the time - no date.

there must be a way to replicate putting in just the time in the first place?..

Author

Commented:
I managed to use 12/30/1899 as a work around - though its not really a satisfactory answer, there must be a 'proper' way of doing it surely?

Thanks for all the help....

Explore More ContentExplore courses, solutions, and other research materials related to this topic.