MS Access project with SQL Server - concatenate date and time

Author

concatenate date and time

JEM

2005-10-31, 1:24 pm

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(datetim
e, CONVERT(nchar, dbo.tblPerf.PerfDate, 101)) As
nvarchar(11)) + ' - ' + CAST(CONVERT(datetim
e,
CONVERT(nchar,dbo.tblPerf.PerfTime, 108)) as nvarchar(15))

Thanks for any help!

JEM

Sylvain Lafontaine

2005-10-31, 1:24 pm

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.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

"JEM" < jennmertens@mindspri
ng.com> wrote in message
news:1130780856.776863.210800@g43g2000cwa.googlegroups.com...
> 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(datetim
e, CONVERT(nchar, dbo.tblPerf.PerfDate, 101)) As
> nvarchar(11)) + ' - ' + CAST(CONVERT(datetim
e,
> CONVERT(nchar,dbo.tblPerf.PerfTime, 108)) as nvarchar(15))
>
> Thanks for any help!
>
> JEM
>

JEM

2005-10-31, 8:25 pm

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

2005-10-31, 8:25 pm

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)

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF

"JEM" < jennmertens@mindspri
ng.com> wrote in message
news:1130787043.675822.134020@g43g2000cwa.googlegroups.com...
> 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
>

JEM

2005-10-31, 8:25 pm

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.

Sponsored Links