Formatting and returning date and time fields. 1

[GShort]

I am sure this is easy for someone.

I have a Date field and a time field but I want to combine them in one on the report but do not know how to return only the date or time from each field. Below is what I am working with.

Date Field
3/24/2005 12:00:00 AM

Time Field
1/1/1900 4:30:00 PM

Wanted Results
3/24/2005 4:30 PM

The seconds don't matter if they show or not.

Thanks for any help.

 

[mrdenny]

You use the convert function for both then combine the strings.

declare @DateField datetime
declare @TimeField datetime

set @DateField = '3/24/2005 12:00:00 AM'
set @TimeField = '1/1/1900 4:30:00 PM'

/*To get the date*/
select convert(varchar(10), @DateTime, 101)
/*To Get the time*/
select convert(varchar(10), @TimeField, 108)
/*To Combine*/
select convert(varchar(10), @DateTime, 101) + ' ' + convert(varchar(10), @TimeField, 108)

You can use this inline in a select statement with a table as well.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[xlbo]

In the report, you should just be able to add the 2 together. Dates are integers and times are decimal so the data behind your 'Date' will be a number like 38611 (today)
This will show as 16/09/2005 12:00 AM as 12:00 AM is = 0 in time notation.

Times are decimals (as they are part of a day) so midday is 0.5, 6pm is 0.75 etc etc - you can see therefore how you should just be able to add the 2 fields together to get a numeric result and simply format as required...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GShort]

Thank you for the help. What I did was use the convert functions that mrdenny gave me and converted the the date and time in the SQL query and from there it worked great.

Thanks

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief