sql datetime

Soldato
Joined
18 Oct 2002
Posts
4,925
Location
Yorkshire
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
 
Last edited:
WHat SQL are you using. have a look at the manual, datepart in SQL server would do it as you could just do


Table1
-------
appDate | appTime

Table2
---------
AppDateTime


AND datepart(year,t1.appDate) = datepart(year,t2.AppDateTime)
AND datepart(month,t1.appDate) = datepart(month,t2.AppDateTime)
AND datepart(day,t1.appDate) = datepart(day,t2.AppDateTime)
AMD datepart(hour,t1.appTime) = datepart(hour,t2.AppDateTime)
AMD datepart(minute,t1.appTime) = datepart(minute,t2.AppDateTime)
AMD datepart(second,t1.appTime) = datepart(second,t2.AppDateTime)
AMD datepart(millisecond,t1.appTime) = datepart(millisecond,t2.AppDateTime)

Because you are concatenating it will probably convert to a string, hence the 00:00:00.000
 
would I have to do all them comparisons ?? seems a little long winded having to do it on the day, month, year, hours, mins, secs etc.

So theres no way for me to take a datetime field which has got 25/08/2007
and a datetime field which has got 13:25:00 and join them up to make
25 Aug 2007 13:25:00:000

?
 
Last edited:
think I might have cracked it, with the following

Declare @myDate as datetime,
@myTime as datetime
SET @myDate = '08/26/2007'
SET @myTime = '09:15:00'

SELECT LEFT(convert(varchar,@myDate,13), 12) + convert(varchar,@myTime,108) as joind

That seems to be spitting out the correct format will test it now in the correct sp
 
:( well still having issue

made a little mistake in my orig post.

in the seccond table the datetime is in the format of

25/082007 09:35:00 NOT 25 Aug 2007 09:35:00
 
just get DATE_FORMAT is not a recognised function.
You sure that functions not for MYSQL and not SQL Server ?
 
Back
Top Bottom