How to merge Date and Time fields into "yyyy-mm-dd hh:mm:ss"?

I'm trying to create an SQL statement which merges data from date and time column to become a date + time column.

Both date and time column in my database uses nvarchar(50) as the data type and I'm using the following SQL:

select A.[Inv No], A.[Project No], [Date + Time], A.[Description], A.[Problem + Repair Details], 
A.[Status], A.[Accumulative Count], A.[Preventive Count], A.[PIC] 
from [SQLtest].[dbo].[Die_Entry] A 
left join (select [Inv No], max(Date + Time) as [Date + Time] 
from [SQLtest].[dbo].[Die_Entry] 
group by [Inv No]) B on A.[Inv No] = B.[Inv No] where [Date + Time] = Date + Time 
Order by A.[Status], A.[Date]desc

Open in new window


Say for example I have the date field = "3-4-2018" and a time field = "8:00:00 AM".

Ideally I want the Date + Time field to be "3-4-2018 8:00:00 AM". But in SQL's case I get "3-4-20188:00:00 AM".
The lack of space between date and time is causing my Excel program to be unable to tell its a date & time format.

How do I tell SQL to put a space in between date and time?
Hans J.HauAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
If you had 2 fields named Date and Time and you wanted to 'merge' them with a space between this is all you should need.

[Date]+' ' +[Time]

Not sure if Excel will recognise that as a date/time though.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
This should do..
select A.[Inv No], A.[Project No], cast([Date] as datetime) + cast([Time] as datetime), A.[Description], A.[Problem + Repair Details], 
A.[Status], A.[Accumulative Count], A.[Preventive Count], A.[PIC] 
from [SQLtest].[dbo].[Die_Entry] A 
left join (select [Inv No], max(cast([Date] as datetime) + cast([Time] as datetime)) as dttime
from [SQLtest].[dbo].[Die_Entry] 
group by [Inv No]) B on A.[Inv No] = B.[Inv No] where cast([Date] as datetime) + cast([Time] as datetime) = dttime
Order by A.[Status], A.[Date]desc

Open in new window

0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Ideally, you need to convert both Date and time columns to datetime datatype and add it to get it work..
Sample below..
declare @date date = '2018-01-01'
declare @time time = '18:00:00.000'

select cast(@date as datetime) + cast(@time as datetime)

Open in new window

0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

ste5anSenior DeveloperCommented:
Well, as the others already tried to point out: work with correct data types. Then many things get easier. Especially as your MAX() will not work on dates stored as NVARCHAR(). In this case you'll get a lexical sort, thus 5-6-2017 is greater then 3-4-2018.
2
Mark WillsTopic AdvisorCommented:
What datatypes are [date] and [time] ?

by the results you are currently getting, they seem to be character based.

In which case, just put a space in there as suggested by Norrie - apart from max() as shown by ste5an above.

Otherwise, let us know the datatypes and we can come up with a solution - although it looks like Raja may have already considered the alternatives.

I would be inclined to use convert and style codes if converting a string to a date because it could become 3rd April or 4th March and max() will give you the wrong results.
0
Mark WillsTopic AdvisorCommented:
example :
declare @date varchar(10) = '3-4-2018'
declare @time varchar(10) = '8:00:00 AM'

select convert(datetime, @date, 101) + convert(datetime, @time, 108)
 

Open in new window

have a look at style codes : https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql#date-and-time-styles

style code is 101 = mm/dd/yyyy whereas 103 = dd/mm/yyyy

Or....
declare @date varchar(10) = '3-4-2018'
declare @time varchar(10) = '8:00:00 PM'

select convert(datetime, @date+' '+@time,101)

Open in new window

0
HuaMin ChenProblem resolverCommented:
See this example
select CONVERT(datetime,'25/04/2018 12:29:14 PM',103)
go

Open in new window

0
Hans J.HauAuthor Commented:
@Norie

Actually I found that out while waiting for my question to be posted.

Now it works fine.

Thanks though.
0
Mark WillsTopic AdvisorCommented:
just be aware that 3-4-2018 08:00AM is higher / max over 3-4-2018 07:00PM or less that 9-9-2016 10:00PM

e.g.
declare @table table ([date + time] varchar(20))
insert @table values ('3-4-2018 08:00AM'),('3-4-2018 07:00PM'),('9-9-2016 10:00PM')

select max([date + time])
from @table

-- results
(No column name)
9-9-2016 10:00PM

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.