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?
 
NorieVBA ExpertCommented:
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
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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 & ArchitectCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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 ChenSystem AnalystCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.