Date and time in different fields and formats Comparison

[agentwalker]

in one of my tables there are two datetime fields.
One holds the date in the format of 25/08/2007 and the other holds just the time like so 13:25:00.

Now in the second table the date and time is all in one field like so
25 Aug 2007 13:25:00

What i'm trying to do so get the date and time out of the first table and compare it against the one in the second table.

The problems i'm having is due to the date and time being separated in the first table so i need to concatenate these two to compare against the other date.

Table1
-------
appDate | appTime

Table2
---------
AppDateTime

currently trying to concatenate like so:
WHERE Table1.appDate + ' ' + Table1.appTime = Table2.AppDateTime

But its not working, think that sql server is taking the date out of the first table and adding a 00:00:00:000 to the start which is screwing up the concatination.

ANy ideas

 

[agentwalker]

little correction.

in the seccond table the datetime is in the format of

25/082007 09:35:00 NOT 25 Aug 2007 09:35:00

 

[SkipVought]

Hi

Date/Time is not string

WHERE Table1.appDate + Table1.appTime = Table2.AppDateTime

Skip,
[sub]
When a wee mystic is on the loose..
It's a Small Medium at Large! [/sub]

 

[bborissov]

Convert them to one type and them do the comparisons:

declare @test1 char(10)
declare @test2 char(8)
SET @Test1 = '08/27/2007'
SET @Test2 = '13:00:00'

declare @test3 datetime
SET @test3 = CAST('2007/08/27 13:00:00' as datetime)

SELECT CAST(@test1+' '+@Test2 as datetime),
       @Test3

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[gmmastros]

I strongly recommend that you stop storing dates and times in string fields. Instead, take a few moments and get comfortable with the DateTime data type in SQL Server. While it may take a (relatively) short amount of time to learn how to use them effectively, I'm reasonable sure that you will likely save yourself A LOT of time in the future.'

You should compare your data as though they are dates. Try something like...

WHERE Convert(DateTime, Table1.appDate) + ' ' + Convert(DateTime, Table1.appTime) = Convert(DateTime, Table2.AppDateTime)

I wouldn't be surprised if this causes an error message.
[tt][blue]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.[/blue][/tt]

If this happens, it is because you have invalid data stored in your table. This is the danger of using string columns.

-George

"the screen with the little boxes in the window." - Moron

 

[agentwalker]

thanks guys will take your tips and give them a try.

Probably me not being clear and reading out of enterprise manager instead of query analyser. just to recap.

All the dates are stored as DateTime's and not strings
just when reading in enterprise manager the date was only showing on one and the time in the other, but when viewing in query analyser the full thing is shown which was causing me all the problems.

So again there are two datetime fields one which has the date I need and the other which has the time I need. I want to get the bits I need and combine them so there in one datetime variable.

 

[gmmastros]

If one of them ONLY has time, and the other ONLY has date (the time component shows midnight), then feel free to ignore my advice and use SkipVought's advice.

Often times it is helpful to think of DateTime values as a floating point number (it's not stored like that, but helpful to think of it like that). The whole number part of a DateTime value represents the number of days that have elapsed since Jan 1, 1900. The fractional part of the value represents time (fractional part of day). So... noon would be 0.5, 6 am = 0.25, and 6 pm = 0.75.

For example...

Select Convert(float, GetDate())

Given this logic, you can simply add 2 DateTime columns together.

Declare @Date DateTime
Declare @Time DateTime

Set @Date = '20070801'
Set @Time = '14:30:25'

Select @Date, @Time, @Date + @Time

[tt][blue]
----------------------- ----------------------- ------------------------
2007-08-01 00:00:00.000 1900-01-01 14:30:25.000 2007-08-01 14:30:25.000

(1 row(s) affected)

[/blue][/tt]

-George

"the screen with the little boxes in the window." - Moron

 

[agentwalker]

thanks guys all sorted now