locked
Date Value wrong RRS feed

  • Question

  • User-1499457942 posted

    Hi

      I have below code . I am using Sql2008.

    set @m_date0 = cast(convert(datetime,convert(varchar(10),@year) + '/' +
    convert(varchar(10),@month) + '/ 1' , 103) as date);
    SET @m_date1 = DATEADD(d,-1,DATEADD(m, 1, @m_date0))
    SET @m_totDays = DATEDIFF(d, @m_date0, @m_date1)

    When i print @m_date0 & m_date1 values it print

    m_date0 = 2018-01-07 

    m_date1 = 2018-02-06 . In this variable Date should be 2018-08-01

    Thanks

    Thursday, November 8, 2018 1:49 PM

All replies

  • User475983607 posted

    The main problem is you are not setting aside enough time to learn the subject or read the reference documentation.   I'm not sure how to help you as we have provided correct syntax in other threads yet you continue to make up your own custom convoluted approach.

    For example, syntax is simply wrong and tries to cast a DateTime to a Date... Why?

    set @m_date0 = cast(convert(datetime,convert(varchar(10),@year) + '/' +
    convert(varchar(10),@month) + '/ 1' , 103) as date);

    Anyway, if you take a few seconds to debug your code you would see the code is producing a string "2018/7/ 1".

    SELECT convert(varchar(10),@year) + '/' + convert(varchar(10),@month) + '/ 1'

    Then when converted to a DATETIME using 103 which is dd/mm/yyyy not yyyy/MM/ d.  This fact is not hidden or magic and it is clearly covered in the documentation 

    SELECT convert(datetime,convert(varchar(10),@year) + '/' + convert(varchar(10),@month) + '/ 1' , 103)

    You end up with 2018-01-07 00:00:00.000.

    Then converting once again to a DATE type you end up with the same value.

    cast(convert(datetime,convert(varchar(10),@year) + '/' + convert(varchar(10),@month) + '/ 1' , 103) as date);

    I recommend that you follow the previous recommendations and simplify the code to the following assuming @year and @month are both integers.

    set @m_date0 = DATEFROMPARTS(@year, @month, 1)

    This syntax is explained in at least two other of your previous posts.

    https://forums.asp.net/t/2148735.aspx

    https://forums.asp.net/t/2148713.aspx

    JagjitSingh

    When i print @m_date0 & m_date1 values it print

    m_date0 = 2018-01-07 

    m_date1 = 2018-02-06 . In this variable Date should be 2018-08-01

    You'll need to explain how you are coming up with 2018-02-06 as the basic math shown results in the last day of the month.

    This snippet adds one month to 2018-07-01 which is 2018-08-01

    DATEADD(m, 1, @m_date0)

    Then subtract one date which results in 2018-07-31

    SET @m_date1 = DATEADD(d,-1,DATEADD(m, 1, @m_date0))

    Below is code that you can play with but I think you have other design issues because your expected results are far from how the code works.

    DECLARE @m_date0 DATETIME
    DECLARE @m_date1 DATETIME
    DECLARE @m_totDays INT
    DECLARE @year INT = 2018
    DECLARE @month INT = 7
    
    
    --set @m_date0 = cast(convert(datetime,convert(varchar(10),@year) + '/' + convert(varchar(10),@month) + '/ 1' , 103) as date);
    --SELECT convert(varchar(10),@year) + '/' + convert(varchar(10),@month) + '/ 1'
    --SELECT convert(datetime,convert(varchar(10),@year) + '/' + convert(varchar(10),@month) + '/ 1' , 103)
    
    --SELECT @m_date0 AS m_date0
    
    set @m_date0 = DATEFROMPARTS(@year, @month, 1)
    
    SET @m_date1 = DATEADD(d,-1,DATEADD(m, 1, @m_date0))
    SET @m_totDays = DATEDIFF(d, @m_date0, @m_date1)
    
    SELECT @m_date0 AS m_date0, @m_date1 AS m_date, @m_totDays AS m_totDays

    Thursday, November 8, 2018 2:37 PM
  • User-1499457942 posted

    Hi

      I cannot use 

    DATEFROMPARTS . I am using 2008.

    Thanks
    Thursday, November 8, 2018 3:36 PM
  • User475983607 posted

    Hi

      I cannot use 

    DATEFROMPARTS . I am using 2008.
    
    Thanks

    Then populate a standard SQL DATETIME formatted string; yyyy-mm-dd. 

    SET @m_date0 = CAST(CAST(@year AS VARCHAR(4)) + '-' + CAST(@month AS VARCHAR(2)) + '-01' AS DATETIME)

    Thursday, November 8, 2018 3:47 PM
  • User753101303 posted

    In particular 'YYYY-MM-DD' for literal date(times) is preferred because it is an ISO standard and doesn't depend on server settings (and you don"t have to mess with convert and use the proper style number) ie :

    SET language us_english
    SELECT CAST('2018-07-01' AS DATE),CAST('01/07/2018' AS DATE)
    
    SET language french
    SELECT CAST('2018-07-01' AS DATE),CAST('01/07/2018' AS DATE)
    

    The first column shows the same value. The 2nd column is NOT the same value (January 7th with us and July 1st with fr). BTW SQL Server Management Studio show date values using this same format to avoid ambiguity for developers. It still doesn't prevent that in a client side application you could use any convention you want to show this same value.

    Thursday, November 8, 2018 5:06 PM
  • User-1716253493 posted

    Try this

    set @m_date0 = cast(convert(datetime,convert(varchar(4),@year) + '/' +
    Right('0' + convert(varchar(2),@month),2) + '/01' , 103) as date);

    string results = '2018/08/01' instead of '2018/8/1'

    '2018' + '/' + RIGHT('0' + '8',2) + '/01' = '2018/08/01'

    '2018' + '/' + RIGHT('0' + '12',2) + '/01' = '2018/12/01'

    Friday, November 9, 2018 1:36 AM