concatenate date and time

[JEM]

Hi. I'm new to this list and to working with ADP's so forgive the
newbie questions! I am in the process of upsizing an Access 2003
database to an Access ADP/SQL Server app and thus need to make lots of
syntax changes to queries, etc. A form has a combo box that
concatenates the date with the time from two fields and i am having
trouble replicating this. Here is what i have, but the time conversion
is not working properly:

CAST(CONVERT(datetime, CONVERT(nchar, dbo.tblPerf.PerfDate, 101)) As
nvarchar(11)) + ' - ' + CAST(CONVERT(datetime,
CONVERT(nchar,dbo.tblPerf.PerfTime, 108)) as nvarchar(15))

Thanks for any help!

JEM

 

[Sylvain Lafontaine]

You forgot to write the number of caracters inside the Convert() function:

CONVERT(nchar (11), dbo.tblPerf.PerfDate, 101))

or:
CONVERT(nvarchar (11), dbo.tblPerf.PerfDate, 101))

The « As nvarchar(11)) » is also superfleous.

 

[JEM]

Thanks, but the result is still the same, the date works fine in the
conversion but the time shows as:

Jan 1 1900 3:00PM

so what i get is:

Jul 8 2000 - Jan 1 1900 3:00 PM

when i want:

Jul 8 2000 - 3:00PM

JEM

 

[Sylvain Lafontaine]

Well, your last conversion is nvarchar(15), so don't expect to have more
than 15 characters displayed. Try something easier to write (and read):

Select CONVERT(nchar (10), dbo.tblPerf.PerfDate, 101) + ' - '
+ CONVERT(nchar (8), dbo.tblPerf.PerfTime, 108)

 

[JEM]

Thanks! I think one of the problems i was having was trying to do this
in the grid view rather than in sql view. In the grid, it kept
converting the whole thing to a string.