Problem with the type datetime

Question

Problem with the type datetime

mclane

Grasshopper

Points: 24
More actions
April 30, 2003 at 6:08 am

#81611

Hello,
I have the following problem :
I have a database with a table. In this table there are two column with datatime type, but the first contains only a Date e the second only a Time. I use a scrip SQL like this to update the table :
INSERT INTO Table (Data,Orario) VALUES ('10/10/2003','10:23:40')
In the column Date, SQL Server store :
10/10/2003 00.00.00.000
in the column Time :
01/01/1900 10.23.40.000
When I do a query to the database with a ASP page, the result is the following :
Date = 10/10/2003
Time = 01/01/1900 10.23.40.000
Why is there 01/01/1900 in Time ? I don't want it. Why isn't there 00.00.00.000 in Date ?
Excuse me for my english, but i'm italian.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 1

SQL Server stores both dates and times in a single datetime datatype. It there is no time, SQL Server defaults to midnight (00:00:00). Similarly, if there is no date, SQL Server defaults to 01/01/1900.

One question - why would you want to store the date and time in separate fields?

Jeremy

mclane Grasshopper Points: 24 More actions · Answer 2

In same situations is better to store date and time in separate fields to execute same type of query to retrieve information from the database.

But my question is :

why the asp page shows only 10/10/2003 for the date (and not 00.00.00) and it shows 01/01/1900 10.23.40.000 for the time ?

Jeremy Kemp SSCertifiable Points: 6754 More actions · Answer 3

Not entirely sure why you don't get a time for the date field, but I tend to use a function to format the date/time into a standard format:

function WebDate(dtDateValue)

if dtDateValue > "" then

WebDate = year(dtDateValue)&"-"&monthName(month(dtDateValue),True)&"-"&right(0&day(dtDateValue),2)

end if

end function

function WebTime(dtDateTimeValue)

WebTime = ""

if dtDateTimeValue > "" then

WebTime = hour(dtDateTimeValue)&":"&right(0&minute(dtDateTimeValue),2)

end if

end function

Jeremy

SQLBill SSC Guru Points: 51440 More actions · Answer 4

Your query could be truncating the time from the column. That's what I would check first.

For example, try this in Query Analyzer:

select getdate()

select convert(varchar(10), getdate(), 120)

-SQLBill

mclane Grasshopper Points: 24 More actions · Answer 5

mclane

Grasshopper

Points: 24

May 1, 2003 at 6:38 am

#455883

Thanks.