0

Good morning

I have an interesting situation:

1 June 2018 = 43252 as Integer 30 May 2018 = 43250 as Integer

I try to convert a date to the first of the respective month (reporting month) with the following code:

DECLARE @InputDate DateTime, @Result  Float, @Date DateTime

Set @InputDate = '2018/06/18 23:13:05'
If isDate(@InputDate) = 0 
   BEGIN
     Set @Result = 0
   END
ELSE
  BEGIN
    Set @Date = DATEFROMPARTS(Year(@InputDate), Month(@InputDate), 1)
    Set @Result = cast (cast(@Date as datetime) as float) 
    Select @Date, @Result
  END

The result for @Date is '2018-06-01 00:00:00.000' as expected but the result for @Result = 43250 which is 30 May 2018. How does this happen?

jarlh
  • 42,561
  • 8
  • 45
  • 63
Ernie van Wyk
  • 33
  • 6
  • `2018-05-30` returns value `43219` for me, which is actually `2018-05-01`. – EzLo Jun 27 '18 at 07:50
  • Excel gives me the following: 43250 = Wednesday, 30 May 2018; 43252 = Friday, 01 June 2018; 43219 = Sunday, 29 April 2018 – Ernie van Wyk Jun 27 '18 at 07:52
  • [This](https://stackoverflow.com/a/23760262/9676724) explains the difference of 2 days between date convert of SQL and Excel – Ajay Gupta Jun 27 '18 at 08:48
  • Thanks Ajay for the link to the difference between Excel and SQL - quite interesting and I did not know that. However, please copy and paste the code above in SQL Studio and execute the code. So Excel is not in question here....the issue is two different dates for the same date in SQL.... – Ernie van Wyk Jun 27 '18 at 08:58
  • 1
    `2018/06/01` is `43250` in SQL and `43252` in excel. Can you provide the date for which you're getting different dates in SQL? – Ajay Gupta Jun 27 '18 at 10:25
  • Ajay, it is those 2 days that stuffed me around. I never knew that Excel was 2 days out to SQL. What I realized I did was to use Excel to convert back to Date and not SQL. Using SQL it displays perfectly. Thank you for the guidance, you really taught me something today. – Ernie van Wyk Jun 27 '18 at 12:17

0 Answers0