Date Value wrong

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

Answer 1

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

Answer 2

User-1499457942 posted

Hi

  I cannot use 

DATEFROMPARTS . I am using 2008.

Thanks

Answer 3

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)

Answer 4

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.

Answer 5

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'