Programming

Categories

  • No categories

SEARCH

Subscribe!

 Subscribe via Feedburner

Tags

Categories

Blogroll


Combining Date and Time in SQL Server

Posted in: Programming by Steve on March 5, 2009

I have been working with Log Parser to go through IIS data from the last week. In working with it, I found that the queries I wanted to run were a little obscure to me, and not having time to mess around with learning a new query language, I just imported the data into SQL.

The W3C logs store the date and time in separate columns, and for one of my queries, I wanted to combine the date part with the time part to create a single datetime field. 

So here’s my function that I created:

CREATE FUNCTION [dbo].[CombineDateTime](@dtDate DATETIME, @dtTime DATETIME)
RETURNS DATETIME
AS
BEGIN
    RETURN @dtDate + CONVERT(CHAR(8), @dtTime, 108)
END

Hope this helps!

Comments

3 Comments
  1. Marc Brooks

    Why not RETURN DATEADD(ms, @dtDate, DATEDIFF(ms, 0, @dtTime))

    Comment by Marc Brooks on March 6, 2009 at 2:38 am

  2. Steve

    Marc,

    When I run this on a my data (2 datetime columns), I get the following error:

    Argument data type datetime is invalid for argument 2 of dateadd function.

    Comment by Steve on March 7, 2009 at 6:07 pm

  3. AW

    I think Marc meant to write, DATEADD(ms, DATEDIFF(ms, 0, @dtTime), @dtDate)

    DECLARE @dtDate DATETIME
    DECLARE @dtTime DATETIME

    SET @dtDate = ’2009-09-29 00:00:00.000′
    SET @dtTime = ’1900-01-01 14:39:32.237′

    SELECT DATEADD(ms, DATEDIFF(ms, 0, @dtTime), @dtDate)

    Comment by AW on September 28, 2009 at 1:40 pm