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

  • Thread starter Thread starter entodude
  • Start date Start date
E

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.
 
entodude said:
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.
Click to expand...

Jet uses & or +
SQL Server uses +.
AS400 uses || or the CONCAT function
 
entodude said:
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.
Click to expand...
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)
 
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.


Bob Barrows said:
entodude said:
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.
Click to expand...
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)
Click to expand...
 
entodude said:
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'?
Click to expand...

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.
Click to expand...

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))
Bob Barrows said:
entodude said:
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.
Click to expand...
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)
Click to expand...
Click to expand...
 
Back
Top