by Joe Bonavit » Sat, 18 May 2002 08:33:27
given that datefield = 5/16/2002 and hourfield = 1
how can I do this in SQLServer 2k plus format it to return as formatted
above?
Thanks.
by oj » Sat, 18 May 2002 09:07:16
select dateadd(h,hourfield,datefield) as dt
from tbname
--
-oj
http://www.rac4sql.net
http://vyaskn.tripod.com
Quote:> I have 1 field which is a datetime and another which is a hour. I want to
> put the 2 together to for a datetime.
> In Oracle I do:
> select datefield + hourfield/24 from mytable
> which returns
> 5/16/2002 1:00:00AM
> given that datefield = 5/16/2002 and hourfield = 1
> how can I do this in SQLServer 2k plus format it to return as formatted
> above?
> Thanks.
by Andrew J. Kell » Sat, 18 May 2002 09:08:21
A Datetime datatype is exactly that, a Date and the Time. Even though you
may have entered only the Date portion or your GUI only displays the Date
portion it always has a Time portion as well. By default if you only enter
the date it will add Midnight (00:00:00) to what ever the date is. If you
always need both the date and time (even if just the hour is relavant) then
there is no need for 2 different columns as the Datetime will hold both
values. If you look in BooksOnline under functions you will see several
that allow you to manipulate or return values from a DateTime datatype. In
your case if all your dateTime values have Midnight for the time portions
you can add the hour to it similar to this:
SELECT DATEADD(hh,HourField,DateField) FROM YourTable
--
Andrew J. Kelly, SQL Server MVP
TargitInteractive
Quote:> I have 1 field which is a datetime and another which is a hour. I want to
> put the 2 together to for a datetime.
> In Oracle I do:
> select datefield + hourfield/24 from mytable
> which returns
> 5/16/2002 1:00:00AM
> given that datefield = 5/16/2002 and hourfield = 1
> how can I do this in SQLServer 2k plus format it to return as formatted
> above?
> Thanks.
by Joe Bonavit » Sun, 19 May 2002 01:53:33
BTW - we don't usually need both the date and time. Most of the time we want
them separate.
> A Datetime datatype is exactly that, a Date and the Time. Even though you
> may have entered only the Date portion or your GUI only displays the Date
> portion it always has a Time portion as well. By default if you only
enter
> the date it will add Midnight (00:00:00) to what ever the date is. If you
> always need both the date and time (even if just the hour is relavant)
then
> there is no need for 2 different columns as the Datetime will hold both
> values. If you look in BooksOnline under functions you will see several
> that allow you to manipulate or return values from a DateTime datatype.
In
> your case if all your dateTime values have Midnight for the time portions
> you can add the hour to it similar to this:
> SELECT DATEADD(hh,HourField,DateField) FROM YourTable
> --
> Andrew J. Kelly, SQL Server MVP
> TargitInteractive
> > I have 1 field which is a datetime and another which is a hour. I want
to
> > put the 2 together to for a datetime.
> > In Oracle I do:
> > select datefield + hourfield/24 from mytable
> > which returns
> > 5/16/2002 1:00:00AM
> > given that datefield = 5/16/2002 and hourfield = 1
> > how can I do this in SQLServer 2k plus format it to return as formatted
> > above?
> > Thanks.
1. Query datetime-field by date when field contains date + time
I have tablefield (sqlserver2000) which datatype is datetime. I have
inserted value "insert into ... datetimefield = {ts '2001-11-24
08:24:12'}..." in it. How can I find (query) that row by date ?
I have tried :
... where datetimefield like {d '2001-11-24'}...
... where datetimefield = {d '2001-11-24'}...
... where datetimefield like {d '2001-11-24%'}...
... where datetimefield like {d '%2001-11-24%'}...
..but query never returns that row. Whats wrong ?!?!
This works :
... where datetimefield = {ts '2001-11-24 08:24:12'}...
but time is unknown in "real life".
Connectionstring is like:
"Provider=SQLOLEDB;Data Source=SERVERNAME;Initial
Catalog=tempdbase;User Id=sa;Password="
And same problem in access database ?
... where datetimefield = #11/24/2001#...
... where datetimefield like #11/24/2001#...
Maybe its better to use string-datatype instead datetime...
Jaska
2. newbie - Row cannot be located for updating
3. Treating datetime fields as separate date and time fields
5. Comparing datetime field (containing date, time values) to a date only string
6. DB2 and Internet Based Reporting
7. Storing the date and time in a datetime field
8. Current date and time as the Default Value for a DateTime Field
9. java.sql.Date producing a time element when passed to SQL Server datetime field
10. how do I separately retrieve the time and date from a datetime field
11. Time Without the Date in datetime field
12. insert datetime based on a date field + fix time (12:00 noon)