0

I have a SQL query below and its getting 1 day behind date from CRM insurance type expiry dates in the SQL query. The expiry dates for insurance types in CRM are 04/04/2017 and in the query its 03/04/2017, please advise do I use UTC now? 

select CASE mm_insurance.mm_insurancetype   

WHEN 930720000 THEN 'Airside Liability Insurance' 
WHEN 930720001 THEN 'Contractors All Risks' 
WHEN 930720002 THEN 'Employers Liability Insurance' 
WHEN 930720003 THEN 'Product Liability Insurance' 
WHEN 930720004 THEN 'Professional Indemnity Insurance' 
WHEN 930720005 THEN 'Public Liability Insurance' 
WHEN 7930720006 THEN 'Removals and Storage Liability'

ELSE ''

END AS InsuranceType, Format(mm_insurance.mm_expirydate , 'dd/MM/yyyy') as InsuranceExpiry

from mm_insurance where mm_account =  '40600D69-068A-E611-810E-005056956082' and mm_expirydate between '03/04/2017' AND '05/04/2017'

you can see the results as 03/04/2017 when I should be getting 04/04/2017

results is 03/04/2017 when I should be getting 04/04/2017

Ivan Starostin
  • 8,798
  • 5
  • 21
  • 39
  • 1
    If by *CRM* you are referring to MS Dynamics CRM: Yes, those dates are saved as UTC dates. – Filburt Jun 27 '17 at 09:54
  • 1
    Check [Convert Datetime column from UTC to local time in select statement](https://stackoverflow.com/q/8038744/205233) and see if it fixes your issue. – Filburt Jun 27 '17 at 09:56
  • If you're using SQL 2016 : It has inbuilt support for time zone conversion. https://learn.microsoft.com/en-us/sql/t-sql/queries/at-time-zone-transact-sql – Prabhat G Jun 27 '17 at 11:04

3 Answers3

0

This should guide you towards solution : 

Declare @Input_Utc_Date as Datetime = GETUTCDATE();
Select 
DATEADD(MILLISECOND,DATEDIFF(MILLISECOND,getutcdate(),GETDATE()),@Input_Utc_Date) as UTC_To_LocalTime

output : 

input(UTC)               UTC_To_LocalTime 
2017-06-27 10:54:25.480 | 2017-06-27 16:24:25.480
Prabhat G
  • 2,974
  • 1
  • 22
  • 31
0

This is also a solution to convert UTC to local time.

Declare @myDateUtc datetime = '2017-06-27 12:00'

select GETDATE()-GETUTCDATE()+@myDateUtc myDateLocal
Peter
  • 850
  • 5
  • 16
0

Try this query, this should be what your after, its advised when reports are developed to use filtered views.

select CASE mm_insurance.mm_insurancetype   

WHEN 930720000 THEN 'Airside Liability Insurance' 
WHEN 930720001 THEN 'Contractors All Risks' 
WHEN 930720002 THEN 'Employers Liability Insurance' 
WHEN 930720003 THEN 'Product Liability Insurance' 
WHEN 930720004 THEN 'Professional Indemnity Insurance' 
WHEN 930720005 THEN 'Public Liability Insurance' 
WHEN 7930720006 THEN 'Removals and Storage Liability'

ELSE ''

END AS InsuranceType, Format(mm_expirydate , 'dd/MM/yyyy') as InsuranceExpiry

from Filteredmm_insurance AS mm_insurance where mm_account = @AccountId and mm_expirydate between @ExpiryClauseFrom AND @ExpiryClauseTo