SQL Server query: Concatenating two strings and converting to datetime changes the format thereby Datedifference calculated is wrong

Question

Below is the query which I am trying to run:

SELECT 
    CONVERT(DATETIME,('12/1/2016' +' '+ '2:00:00')) AS A, 
    CONVERT(DATETIME,('12/1/2016' +' '+ '2:00:00')) AS B,
    DATEDIFF(HOUR, CONVERT(DATETIME, ('12/1/2016' + ' ' + '2:00:00')), CONVERT(DATETIME, ('12/1/2016' + ' ' + '2:00:00'))) as DateDiffernce

Output is:

A                            B                          DateDiffernce
2016-12-01 02:00:00.000     2016-12-01 02:00:00.000     0

Let's take 12/1/2016 here the format is DD/MM/YYYY. After using convert function it is getting changed to YYYY-MM-DD which is fine the problem is places are changed i.e 12/1/2016 to 2016-12-01 12 which was the date is shifted or considered as month - which should not happen.

I even tried another query to restrict this conversion which didn't help:

SELECT 
    CONVERT(DATETIME, CONVERT(CHAR(10), '12/1/2016', 112) 
    + ' ' + CONVERT(CHAR(8), '2:00:00', 108))

Kindly help..

*Why* aren't you using date and time types? You can use date and time-typed parameters to call a stored procedure or execute a query. You can use date types for fields. There is no reason to deal with strings. One reason - why do you *assume* that 12/1 is January 12 instead of December 1 and why should the server? Why do you assume there is some specific date format in the first place? — Panagiotis Kanavos, Aug 12 '16 at 11:47
The datedifference would be zero only. Look at the hour value both start and end dates are similar with same time — StackUser, Aug 12 '16 at 11:50
I assume you show `YYYY-MM-DD` using SSMS. The query didn't change anything, this is simply the display format used by SSMS to display dates. In fact, it's the only *meaningful* format because it's unambiguous what each value means. You should remove *all* guesswork from your code by using proper date and time types and avoid parsing from/to strings completely — Panagiotis Kanavos, Aug 12 '16 at 11:52
@ Panagiotis Kanavos Thankyou i am not supposed to use stored procedure as in the project i am compelled to use only query. BTW lets leave date difference as of now ... here date 12/1/2016 is getting converted to 2016-12-01 this is actually causing problem as date is gone in months place — mano, Aug 12 '16 at 11:58
@Panagiotis Kanavos if i use `SET DATEFORMAT YDM` then there is no error popping up and this solves my issue if any other solution then really appreciated. — mano, Aug 12 '16 at 12:10
@mano no it doesn't solve any problem. It just hides it until someone connects to your application from the US. Or a developer that uses a US locale. The real problem is using text instead of the correct types. You can combine a date and time value [simply by using addition](http://stackoverflow.com/questions/700619/how-to-combine-date-from-one-field-with-time-from-another-field-ms-sql-server) — Panagiotis Kanavos, Aug 12 '16 at 12:59
Someone please address my question here http://stackoverflow.com/q/38916894/6709147 — Prashant Gupta, Aug 14 '16 at 11:19

StackUser · Accepted Answer · 2016-08-12T12:44:21.627

1

Please check @Panagiotis Kanavos comment. He is right. Your query would work fine. Please change the time value like the below to get the DateDifference value.

  SELECT CONVERT(DATETIME, ('13/1/2016' + ' ' + '2:00:00'),103) AS A
    ,CONVERT(DATETIME, ('13/1/2016' + ' ' + '13:00:00'),103) AS B
    ,DATEDIFF(HOUR, CONVERT(DATETIME, ('13/1/2016' + ' ' + '2:00:00'),103), CONVERT(DATETIME, ('13/1/2016' + ' ' + '13:00:00'),103)) AS DateDiffernce

edited Aug 12 '16 at 12:44

answered Aug 12 '16 at 11:54

StackUser

5,370
2
24
44

if i change the date to 13/1/16 the i get an error this would explain my problem quiet well. `Msg 242, Level 16, State 3, Line 66 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.` – mano Aug 12 '16 at 12:08
I edited my answer, I missed 103 style in convert function. I added now. Please check and let me know the status. – StackUser Aug 12 '16 at 12:44
Yes 105 does the magic my modified query is `SELECT DATEDIFF(MINUTE, CONVERT(DATETIME, '13/1/2016', 105) + CONVERT(DATETIME,'2:00:00', 108), CONVERT(DATETIME, '13/1/2016', 105) + CONVERT(DATETIME,'2:30:00', 108)) AS DateDiffernce` – mano Aug 17 '16 at 09:47

score 1 · Answer 2 · answered Aug 12 '16 at 12:39

1

Hello All Thankyou for your inputs, i was able to solve the issue by using following code

SET DATEFORMAT YDM

Cheers!! Enjoy coding

answered Aug 12 '16 at 12:39

mano

308
3
19

SQL Server query: Concatenating two strings and converting to datetime changes the format thereby Datedifference calculated is wrong

2 Answers2