I intend to convert @ldtEffDt  to dateformat of 107-august 17,2015

but the final output still comes out in the datetimeformat

DECLARE  @lnRetCd int
DECLARE  @lxMsg                   varchar  (255)      

DECLARE  @lnMEME_CK int
DECLARE  @lnSBSB_CK int
DECLARE  @lnGRGR_CK int

DECLARE  @lxCSPD_CAT char(1)
DECLARE  @lxCSPI_ID char(8)
DECLARE @ldtEffDt datetime
DECLARE  @lxUSUS_ID    char(010)
DECLARE @lxSubProcMsg            varchar(255)

SELECT @lnRetCd = 0
SELECT @lxUSUS_ID = ''
SELECT @ldtEffDt = GETDATE()
SELECT @lxCSPD_CAT = 'M'
SELECT @lxCSPI_ID = ' '



SELECT
  @lxCSPI_ID = MEEL.CSPI_ID,
  @ldtEffDt = convert(char(10),MEEL.MEEL_EFF_DT,107)
  FROM
  dbo.CMC_MEEL_ELIG_ENT MEEL
  WHERE
  MEEL.MEME_CK        =  111111 AND
  MEEL.CSPI_ID        >  ' '        AND
  MEEL.CSPD_CAT       =  '222' AND
  MEEL.MEEL_VOID_IND <> 'Y'       AND
  MEEL.MEEL_ELIG_TYPE <> 'TM'       AND
  MEEL.MEEL_ELIG_TYPE <> 'SE'       AND
  MEEL.MEEL_ELIG_TYPE <> 'CE'       AND
  MEEL.MEEL_EFF_DT    =  (SELECT
 max(MEEL_EFF_DT)
  FROM
 dbo.CMC_MEEL_ELIG_ENT MEEL_SUB
  WHERE
 MEEL_SUB.MEME_CK        =  '2222' AND
 MEEL_SUB.CSPI_ID        >  ' '        AND
 MEEL_SUB.CSPD_CAT       =  'M' AND
 MEEL_SUB.MEEL_ELIG_TYPE <> 'TM'       AND
 MEEL_SUB.MEEL_ELIG_TYPE <> 'SE'       AND
 MEEL_SUB.MEEL_ELIG_TYPE <> 'CE'       AND
 MEEL_SUB.MEEL_EFF_DT    <= GETDATE()
 )


 SELECT  @lxCSPI_ID
  SELECT @ldtEffDt 

you need to put the convert in the last select

SELECT convert(char(10),@ldtEffDt ,107)

• Edited by Bendare2 Friday, March 13, 2015 3:33 PM

DECLARE @ldtEffDt datetime
 .......

 SELECT
  @lxCSPI_ID = MEEL.CSPI_ID,
  @ldtEffDt = convert(char(10),MEEL.MEEL_EFF_DT,107)

Data type conflict. You need to declare @ldtEffDt as string:

DECLARE @ldtEffDt char(10)

Datetime conversions: http://www.sqlusa.com/bestpractices/datetimeconversio

Format() - new to SQL SERVER 2012 or later - allows you to format an input value to a character string based on a .NET format string, with an optional culture parameter:

Example:

SELECT top (3) orderid, FORMAT(orderdate,'d','en-us') AS us, FORMAT(orderdate,'d','de-DE') AS de FROM Sales.Orders;

Returns:

Ordered    us               de
-------       --------       ----------
10248       7/4/2006    04.07.2006
10249       7/5/2006    05.07.2006
10250       7/8/2006    08.07.2006

The following link provide an overview of all Transact-SQL date and time data types and functions.

https://technet.microsoft.com/en-us/library/ms186724(v=sql.110).aspx

In order to use date and time data in your queries, you will need to be able to represent temporal data in T-SQL. SQL Server doesn't offer a specific option for entering dates and times, so you will use character strings called literals, which are delimited with single quotes. SQL Server will implicitly convert the literals to date and time values. (You may also explicitly convert literals with the T-SQL CAST function) SQL Server can interpret a wide variety of literal formats as dates, but for consistency and to avoid issues with language or nationality interpretation, it is recommended that you use a neutral format such as 'YYYYMMDD'. To represent February 12, 2012, you would use the literal '20120212'. To use literals in a query, see the following example:

SELECT orderid, custid, empid, orderdate FROM Sales.Orders WHERE orderdate = '20070825';

DECLARE @ldtEffDt DATETIME
SELECT @ldtEffDt = GETDATE()
--in this assignment, the char(12) is implicitly converted to datetime, datetime doesn't include format information
SELECT @ldtEffDt = CONVERT(char(12),CAST('2013-03-03' AS DATE),107)
--datetime doesn't include format information
SELECT @ldtEffDt
/*
OUTPUT 
2013-03-03 00:00:00.000
*/
GO

DECLARE @ldtEffDt CHAR(12)
SELECT @ldtEffDt = GETDATE()
SELECT @ldtEffDt = CONVERT(char(12),CAST('2013-03-03' AS DATE),107)
SELECT @ldtEffDt
/*
Mar 03, 2013
*/