Joining date and time

Hi all,
I need some help with joining two fields of type datetime, one with date
relevancy and the other with time.
If i join the integer part of date field with the fraction part of time
field, the joined datetime is not the same.
What's the trick here?
TIA, JozzaOne way... taking date from @.a, time from @.b
declare @.a datetime, @.b datetime
set @.a = getdate()-1
set @.b = dateadd(hh,5,getdate())
select @.a, @.b,dateadd(ms,datediff(ms,convert(varcha
r(10),@.b,101),@.b),
convert(varchar(10),@.a,101))
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Can you show us an example? What do you mean by "fraction part of time?"
Keith Kratochvil
"Jozza" <hmm@.hmm.com> wrote in message
news:lrcjg.3576$oj5.1220262@.news.siol.net...
> Hi all,
> I need some help with joining two fields of type datetime, one with date
> relevancy and the other with time.
> If i join the integer part of date field with the fraction part of time
> field, the joined datetime is not the same.
> What's the trick here?
> TIA, Jozza
>|||I thought that datetime is stored the way that integer part of a float
represents the date and the fraction part represents the time.
So adding them together would join them. But it doesn't seem to be the case
on SLQ server.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OvXKJ0hjGHA.1508@.TK2MSFTNGP04.phx.gbl...
> Can you show us an example? What do you mean by "fraction part of time?"
> --
> Keith Kratochvil
>
> "Jozza" <hmm@.hmm.com> wrote in message
> news:lrcjg.3576$oj5.1220262@.news.siol.net...
>|||Converting fields to varchar, concatenate strings and convert it back to
datetime does the trick. (which was not exactly what your exemple was, but i
got the idea)
Is there any other way where i could add fields together in mathematical
terms, because i suspect there could be and error in string conversions when
different locale formats are used. Or am i wrong?
Thanks, Jozza
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:22B16AB3-DF86-4878-B181-727F79448589@.microsoft.com...
> One way... taking date from @.a, time from @.b
> declare @.a datetime, @.b datetime
> set @.a = getdate()-1
> set @.b = dateadd(hh,5,getdate())
> select @.a, @.b,dateadd(ms,datediff(ms,convert(varcha
r(10),@.b,101),@.b),
> convert(varchar(10),@.a,101))
>
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>|||Well concatenating the strings might lead to wrong date format if the string
format changes. Thats why I didn't go for the concatenation.
And the example I gave was in mathematical terms :)
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||After looking at the example a little bit longer i realize that you are
absolutely correct.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:33203119-C451-4C10-9733-F8D9EA1B0229@.microsoft.com...
> Well concatenating the strings might lead to wrong date format if the
> string
> format changes. Thats why I didn't go for the concatenation.
> And the example I gave was in mathematical terms :)
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>
>