concatenate date and time

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JEM - 31 Oct 2005 18:47 GMT

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

Reply to this Message

Sylvain Lafontaine - 31 Oct 2005 18:55 GMT

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.

Signature

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

> 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
[quoted text clipped - 11 lines]
>
> JEM

Reply to this Message

JEM - 31 Oct 2005 20:30 GMT

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

Reply to this Message

Sylvain Lafontaine - 31 Oct 2005 21:37 GMT

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)

Signature

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

> Thanks, but the result is still the same, the date works fine in the
> conversion but the time shows as:
[quoted text clipped - 10 lines]
>
> JEM

Reply to this Message

JEM - 31 Oct 2005 23:20 GMT

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.

Reply to this Message