Access pass-through query, SQL-92 combining strings: '&' does not

[entodude]

What is the SQL-92 equivalent to Access '&' when combining strings? Such as
getting a Full Name using Access is [First] & " " & [Last].

Specifically, I am trying to get a date in format mm/dd/yyyy.

I want Access Date(); I am using SQL-92
CONVERT(DATETIME,CONVERT(varchar,GETDATE())). This returns the Access
equivalent of Now(), with the time stamp, which I do not need.

I tried using the DATEPART(mm, CONVERT(DATETIME,CONVERT(varchar,GETDATE())))
and the dd and yyyy of same function and putting it all together as
mm/dd/yyyy.

I can not find documentation on the SQL-92 method of joining strings.

 

[Bob Barrows]

What is the SQL-92 equivalent to Access '&' when combining strings?
Such as getting a Full Name using Access is [First] & " " & [Last].

Specifically, I am trying to get a date in format mm/dd/yyyy.

I want Access Date(); I am using SQL-92
CONVERT(DATETIME,CONVERT(varchar,GETDATE())). This returns the Access
equivalent of Now(), with the time stamp, which I do not need.

I tried using the DATEPART(mm,
CONVERT(DATETIME,CONVERT(varchar,GETDATE()))) and the dd and yyyy of
same function and putting it all together as mm/dd/yyyy.

I can not find documentation on the SQL-92 method of joining strings.

Jet uses & or +
SQL Server uses +.
AS400 uses || or the CONCAT function

 

[Bob Barrows]

What is the SQL-92 equivalent to Access '&' when combining strings?
Such as getting a Full Name using Access is [First] & " " & [Last].

Specifically, I am trying to get a date in format mm/dd/yyyy.

I want Access Date(); I am using SQL-92
CONVERT(DATETIME,CONVERT(varchar,GETDATE())). This returns the Access
equivalent of Now(), with the time stamp, which I do not need.

I tried using the DATEPART(mm,
CONVERT(DATETIME,CONVERT(varchar,GETDATE()))) and the dd and yyyy of
same function and putting it all together as mm/dd/yyyy.

Oh wait, I answered the question stated in your first sentence without
reading the rest of your post ...

That is SQL Server, correct? Use the style argument of the CONVERT
function to get what you want:

CONVERT(char(10),GETDATE(),101)

 

[entodude]

Thanks.
CONVERT(CHAR(10),GETDATE(),101) worked on today's date.

Will this CHAR(10) work when the month or day is < 10 (only 1 character) or
do I need to trap for that? In other words, will SQL Server pass back '01'
when the number is '1'?

On the string concatenation, I tried the plus sign (+) and it failed with a
message that the function was missing an argument. I figured it was the plus
that was throwing it off.

What is the SQL-92 equivalent to Access '&' when combining strings?
Such as getting a Full Name using Access is [First] & " " & [Last].

Specifically, I am trying to get a date in format mm/dd/yyyy.

I want Access Date(); I am using SQL-92
CONVERT(DATETIME,CONVERT(varchar,GETDATE())). This returns the Access
equivalent of Now(), with the time stamp, which I do not need.

I tried using the DATEPART(mm,
CONVERT(DATETIME,CONVERT(varchar,GETDATE()))) and the dd and yyyy of
same function and putting it all together as mm/dd/yyyy.

Oh wait, I answered the question stated in your first sentence without
reading the rest of your post ...

That is SQL Server, correct? Use the style argument of the CONVERT
function to get what you want:

CONVERT(char(10),GETDATE(),101)

 

[Bob Barrows]

Thanks.
CONVERT(CHAR(10),GETDATE(),101) worked on today's date.

Will this CHAR(10) work when the month or day is < 10 (only 1
character) or do I need to trap for that? In other words, will SQL
Server pass back '01' when the number is '1'?

Yes - it is simple enough to try it:
select CONVERT (char(10),CAST('20081001' as datetime),101)

On the string concatenation, I tried the plus sign (+) and it failed
with a message that the function was missing an argument. I figured
it was the plus that was throwing it off.

Show me what you tried. You do need to be aware that you need to be sure
the operands are both strings - if you are using DATEPART, it returns an
int, so you have to cast it as a string: either char or varchar - don't
forget to specify the length of the string in the cast statement.

SELECT CAST(DATEPART(m, GETDATE()) as varchar(9)) + '/' +
CAST(DATEPART(d, GETDATE()) as varchar(9)) + '/' +
CAST(DATEPART(yyyy, GETDATE()) as varchar(9))

What is the SQL-92 equivalent to Access '&' when combining strings?
Such as getting a Full Name using Access is [First] & " " & [Last].

Specifically, I am trying to get a date in format mm/dd/yyyy.

I want Access Date(); I am using SQL-92
CONVERT(DATETIME,CONVERT(varchar,GETDATE())). This returns the
Access equivalent of Now(), with the time stamp, which I do not
need.

I tried using the DATEPART(mm,
CONVERT(DATETIME,CONVERT(varchar,GETDATE()))) and the dd and yyyy of
same function and putting it all together as mm/dd/yyyy.

Oh wait, I answered the question stated in your first sentence
without reading the rest of your post ...

That is SQL Server, correct? Use the style argument of the CONVERT
function to get what you want:

CONVERT(char(10),GETDATE(),101)