October 11, 2009 at 8:56 am
Hello All,
I need to write a Stored Procedure to interface data between 2 tables in SQL Server 2000.
One of the tables has a field called PICKUP_TIME of type DATETIME. The data in this field is as follows:
ID | PICKUP_TIME
1 | 11/09/2009 19:23:30.000
2 | 12/09/2009 07:12:20.000
I need to extract the time part (19:23:20) of the 'PICKUP_TIME' field and push it to another table in the following format:
01/01/1900 19:23:30
01/01/1900 07:12:20
I would appreciate if someone could help me.
thanks
Noel
October 11, 2009 at 9:48 am
The DATEADD / DATEDIFF expression below is one efficient method to remove the date component from a datetime value, that is, set the date component value to zero (1900-01-01).
SELECT DATEADD(day, DATEDIFF(day, PICKUP_TIME, 0), PICKUP_TIME)
FROM (
SELECT GETDATE()
) T(PICKUP_TIME)
so your INSERT statement would be something like:
INSERT INTO DestinationTable(PICKUP_TIME, ...)
SELECT DATEADD(day, DATEDIFF(day, PICKUP_TIME, 0), PICKUP_TIME), ...
FROM SourceTable
October 11, 2009 at 12:43 pm
Hello Andrew,
Thanks for your reply.
Your solution does extract the correct time with the date set to zero, however the result that I get still has the Micro seconds. It looks as follows:
1900-01-01 15:30:00.000
I need it without the Micro seconds.
October 11, 2009 at 5:10 pm
I presume you are storing the time-only value as a datetime column in the destination table rather than a character string. If so, removing the milliseconds (not microseconds!) component is just a matter of formatting the stored datetime value. This is usually best done in the front-end application, but you can also use the CONVERT function to do this in the query output. Lookup the CONVERT function in SQL Server Books Online for the various formatting options. I've shown a couple of examples below: YYYY-MM-DD HH:MM:SS, which uses style 120, and DD/MM/YYYY HH:MM:SS, which uses styles 103 and 108.
You can also remove the milliseconds from the stored datetime value as demonstrated by the expression used to calculate the TIMEONLY_WITHOUT_MSEC column below.
SELECT
[NOW],
TIMEONLY,
TIMEONLY_WITHOUT_MSEC,
CONVERT(char(19), TIMEONLY_WITHOUT_MSEC, 120) AS [YYYY-MM-DD HH:MM:SS],
CONVERT(char(10), TIMEONLY_WITHOUT_MSEC, 103) + ' ' + CONVERT(char(8), TIMEONLY_WITHOUT_MSEC, 108) AS [DD/MM/YYYY HH:MM:SS]
FROM (
SELECT
[NOW],
DATEADD(day, DATEDIFF(day, [NOW], 0), [NOW]) AS TIMEONLY,
DATEADD(second, DATEDIFF(second, DATEADD(day, DATEDIFF(day, 0, [NOW]), 0), [NOW]), 0) AS TIMEONLY_WITHOUT_MSEC
FROM (
SELECT GETDATE() AS [NOW]
) S
) D
October 11, 2009 at 10:07 pm
webnoel (10/11/2009)
1900-01-01 15:30:00.000I need it without the Micro seconds.
To get the exact result use the smalldatetime.
declare @date datetime
set @date = getdate()
SELECT @date = cast(DATEADD(D, DATEDIFF(D, @date, 0), @date) as smalldatetime)
select @date
October 12, 2009 at 7:10 am
Hello Andrew/Arun
Thanks guys.. I found the solution in both your posts... i.e. Andrew's 1st Post and Arun's
So!! My query looks like this....
SELECT CAST(DATEADD(day, DATEDIFF(day, TB_BOOKING_ITEMS.PICKUP_TIME, 0), PICKUP_TIME) AS SMALLDATETIME) FROM TABLENAME
and this seems to work just fine for me.... so thanks once again.
Cheers
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply