locked
date format RRS feed

  • Question

  • 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 

       
    Friday, March 13, 2015 3:23 PM

Answers

  • Dates should be formated in the presentation layer, not in the database.

    https://msdn.microsoft.com/en-us/library/ms187928.aspx?f=255&MSPPError=-2147217396

    There are styles to be used. Looks like the one you want is 107, through this will abbreviate the month to three characters.

    If you want to force your format try this:

    DATENAME(MONTH, CLCL_PAID_DT) +' '+ RIGHT('00' + DATEPART(DAY,CLCL_PAID_DT),2)+', '+CAST(DATEPART(YEAR,CLCL_PAID_DT) AS VARCHAR)


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

    • Proposed as answer by KEAARPEE Tuesday, March 17, 2015 6:06 PM
    • Marked as answer by Dre01 Tuesday, March 17, 2015 9:52 PM
    Tuesday, March 17, 2015 5:24 PM

All replies

  • you need to put the convert in the last select

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

    • Proposed as answer by KEAARPEE Tuesday, March 17, 2015 5:55 PM
    Friday, March 13, 2015 3:30 PM
  • Could you provide examples of the source date from MEEL.MEEL_EFF_DT?

    • Edited by Bendare2 Friday, March 13, 2015 3:33 PM
    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/datetimeconversion/




    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014





    • Edited by Kalman Toth Friday, March 13, 2015 3:44 PM
    • Proposed as answer by Eric__Zhang Monday, March 16, 2015 6:51 AM
    Friday, March 13, 2015 3:43 PM
  • 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';




    Ricardo Lacerda


    Friday, March 13, 2015 4:08 PM
  • Hi Dre01,

    You can compare the below samples.

    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
     /*
     2013-03-03 00:00:00.000
     */
     GO

    -- use string datatype if you want the format included
     DECLARE @ldtEffDt CHAR(12)
     SELECT @ldtEffDt = GETDATE()
     SELECT @ldtEffDt = CONVERT(char(12),CAST('2013-03-03' AS DATE),107)
     SELECT @ldtEffDt
     /* Mar 03, 2013 */


    If you have any feedback on our support, you can click here.

    Eric Zhang
    TechNet Community Support



    • Edited by Eric__Zhang Monday, March 16, 2015 7:41 AM
    • Proposed as answer by Eric__Zhang Wednesday, March 18, 2015 1:07 AM
    Monday, March 16, 2015 7:35 AM
  • select convert(char(12),CLCL_PAID_DT ,107) as datepaid
    FROM  [FacetsReport].[dbo].CMC_CLCL_CLAIM clcl 

    gives the format Jan 01, 1953  

    how do I get the format 

    January 01, 1953 ?

    Thanks 

    Tuesday, March 17, 2015 4:35 PM
  • Dates should be formated in the presentation layer, not in the database.

    https://msdn.microsoft.com/en-us/library/ms187928.aspx?f=255&MSPPError=-2147217396

    There are styles to be used. Looks like the one you want is 107, through this will abbreviate the month to three characters.

    If you want to force your format try this:

    DATENAME(MONTH, CLCL_PAID_DT) +' '+ RIGHT('00' + DATEPART(DAY,CLCL_PAID_DT),2)+', '+CAST(DATEPART(YEAR,CLCL_PAID_DT) AS VARCHAR)


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

    • Proposed as answer by KEAARPEE Tuesday, March 17, 2015 6:06 PM
    • Marked as answer by Dre01 Tuesday, March 17, 2015 9:52 PM
    Tuesday, March 17, 2015 5:24 PM
  • @Pygoc, Need size atleast 12

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


    • Edited by KEAARPEE Tuesday, March 17, 2015 5:58 PM
    Tuesday, March 17, 2015 5:56 PM