August 26, 2004 at 2:22 pm
I have a datetime column which for the most part accurately stores the date and time, however 12:00:00 AM on any day stores blank
eg.
10th of January 2004 at 2:00 pm stores as
2004/Jan/10 2:00:00 PM
10th of January 2004 at 12:00 am stores as
2004/Jan/10
should it not at least return
2004/Jan/10 00:00:00 AM
What am I doing wrong. Thanx for da help
August 26, 2004 at 3:18 pm
If your column is of the datetime data type the time element is always there, even if it is all zero's. The issue you are having must be related to either a CONVERT function in your query or to the front end application perfoming some kind of conversion/truncation.
I am not aware of any format option in the CONVERT function that would return that specific date format, so I would guess that the issue is not related to SQL Server per se.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
August 27, 2004 at 6:12 am
How is the date being displayed? Through an application? That date format is not one of built-in formats, so maybe the appliction that is displaying the date is dropping the time at 12 AM because the time value is zero ( 0 ). You may need to modify your application, if possible.
If you print the dates with Query Analyzer, the time probably will show up.
August 27, 2004 at 8:08 am
I agree with the previous posters....it's gotta be your application. The reason is that SQL Server does not STORE dates and times in any "date/time" format.
Per the BOL:
Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.
End quote
It's always the responsibility of the application to format the datetime datatype for display. Therefore, it has to be your application doing the truncation of the time.
-SQLBill
August 27, 2004 at 7:17 pm
OK, this was a little harder than I thought, but this works:
declare @Date datetime,
@Hour int,
@isPM bit
select @Date = '2004-08-27 00:00:00.000',
@hour = datepart(hh, @Date),
@isPM = Case when @Hour > 12 then 1 Else 0 End,
@hour = Case @isPM When 1 then @Hour - 12 Else @Hour End
select convert(varchar,@Date, 100),
datename(yy, @Date) + '/' +
cast(datename(mm, @Date) as char(3)) + '/' +
replicate('0', 2-len(cast(datepart(dd, @Date) as varchar(2)))) +
cast(datepart(dd, @Date) as varchar(2)) + ' ' +
replicate('0', 2-len(@hour)) +
cast(@hour as varchar(2)) + ':' +
replicate('0', 2-len(cast(datepart(mi, @Date) as varchar(2)))) +
cast(datepart(mi, @Date) as varchar(2)) + ':' +
replicate('0', 2-len(cast(datepart(ss, @Date) as varchar(2)))) +
cast(datepart(ss, @Date) as varchar(2)) + ' ' +
Case
When @Hour = 12 and @isPM = 0 Then 'PM'
When @isPM = 1 then 'PM'
Else 'AM'
END
Notice that the "Convert" represents this as "Aug 27 2004 12:00AM", not "0:00 AM".
Signature is NULL
August 29, 2004 at 9:52 pm
should it not at least return
2004/Jan/10 00:00:00 AM
No, there is no 00:00:00 AM. In AM/PM format times go from 01:00:00 to 12:59:59. Depending on whether you meant 12 noon or midnight you will get 12:00:00 AM or PM, though it's ambiguous which is which. If you meant midnight, this (in 24 hr format) is 2004-01-10 00:00:00. Both QA and EM print times at exactly midnight as the date part only: e.g. 2004-01-10 - this may be what you're seeing.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply