Login     Register

        Contact Us     Search

XLeratorDB/financial Documentation

SQL Server MDY to float


DATEFLOAT

Updated: 07 February 2011


Use DATEFLOAT to return a float value for a specified Year, Month, and Day. DATEFLOAT is the equivalent of the DATE function in EXCEL.
Syntax
SELECT [wctFinancial].[wct].[DATEFLOAT](
  <@Year, int,>
 ,<@Month, int,>
 ,<@Day, int,>)
Arguments
@Year
The integer value representing the year. @Year is an expression that returns an int or that can be implicitly converted to int
@Month
The integer value representing the month. @Month is an expression that returns an int or that can be implicitly converted to int
@Day
The integer value representing the day of the month. @Day is an expression that returns an int or that can be implicitly converted to int
Return Type
float
Remarks
·         DATEFLOAT is the equivalent of the DATE function in EXCEL, except that it returns a float representing the number of days from 01-Jan-1900. This is consistent with numeric representation of dates in SQL Server. EXCEL and .NET have different numeric representations of dates than SQL Server.
·         To convert a year, month, and day combination to a datetime, consider using the CALCDATE function.
·         To convert a year, month, and day combination to an integer, consider using the DATEINT function.
Examples
To represent 07-Feb-2011, enter the following SQL.
SELECT wct.DATEFLOAT(
       2011 --@Year
      ,2          --@Month
      ,7          --@Day
      ) as DATEFLOAT
This produces the following result.
             DATEFLOAT
----------------------
                 40579
 
(1 row(s) affected)
 


DATEFLOAT is language independent. The datetime value returned is consistent across different language settings. Look at the following example.
SET LANGUAGE Italian
SELECT wct.DATEFLOAT(2011,2,7) as [DATEFLOAT(2011,2,7)]
,cast(cast('02/07/2011' as datetime) as int) as [cast(cast('02/07/2011' as datetime) as int)]
This returns the following result.
   DATEFLOAT(2011,2,7) cast(cast('02/07/2011' as datetime) as int)
---------------------- -------------------------------------------
                 40579                                       40724
 
(1 row(s) affected)


 
SET LANGUAGE us_english
SELECT wct.DATEFLOAT(2011,2,7) as [DATEFLOAT(2011,2,7)]
,cast(cast('02/07/2011' as datetime) as int) as [cast(cast('02/07/2011' as datetime) as int)]
This returns the following result
   DATEFLOAT(2011,2,7) cast(cast('02/07/2011' as datetime) as int)
---------------------- -------------------------------------------
                 40579                                       40579
 
(1 row(s) affected)


As you can see,the same date string was passed in both statements, but the datetime value returned was different based upon the language setting for the database. Using DATEFLOAT avoids that problem.
DATEFLOAT allows you to perform operation on the date components. You can add or subtract integer values to @Year, @Month, @Date, and still be confident of having a valid value returned.
Let’s say you wanted to generate a schedule that showed the first Monday of every month. Here’s one way to do this in SQL.
SELECT DATEADD(d
,(9 - DATEPART(DW,
cast(
cast(mth as varchar) + '/1/' + cast(2011 as varchar)
                        as datetime
                  )
                  )
                  ) % 7
      ,cast(
            cast(mth as varchar) + '/1/' + cast(2011 as varchar)
                  as datetime
                  )
            ) as [First Monday]
FROM (
      VALUES
            (1), (2), (3), (4), (5), (6),
(7), (8), (9), (10), (11), (12)
            ) m(mth)
This produces the following result.
First Monday
-----------------------
2011-01-03 00:00:00.000
2011-02-07 00:00:00.000
2011-03-07 00:00:00.000
2011-04-04 00:00:00.000
2011-05-02 00:00:00.000
2011-06-06 00:00:00.000
2011-07-04 00:00:00.000
2011-08-01 00:00:00.000
2011-09-05 00:00:00.000
2011-10-03 00:00:00.000
2011-11-07 00:00:00.000
2011-12-05 00:00:00.000
 
(12 row(s) affected)


DATEFLOAT allows you to perform operations on the components of the date rather than on the date value, simplifying the SQL.
SELECT CAST(wct.DATEFLOAT(2011
      ,mth
      ,1 +(9 - DATEPART(DW,wct.DATEFLOAT(2011,mth,1))) % 7
      ) as datetime) as [First Monday]
FROM (
      VALUES
            (1),(2),(3),(4),(5),(6),
            (7),(8),(9),(10),(11),(12)
            ) m(mth)
This produces the following result.
First Monday
-----------------------
2011-01-03 00:00:00.000
2011-02-07 00:00:00.000
2011-03-07 00:00:00.000
2011-04-04 00:00:00.000
2011-05-02 00:00:00.000
2011-06-06 00:00:00.000
2011-07-04 00:00:00.000
2011-08-01 00:00:00.000
2011-09-05 00:00:00.000
2011-10-03 00:00:00.000
2011-11-07 00:00:00.000
2011-12-05 00:00:00.000
 
(12 row(s) affected)


As a side note, the reason that we used 9 to represent Monday and did not use 2 in this SQL (2 being the numeric representation of Monday), is that the modulo function, %, in SQL Server is really a remainder function. The SQL would look more intuitive if we had used the XLeratorDB modulo function (which is the equivalent of the EXCEL MOD function).
SELECT CAST(wct.DATEFLOAT(2011
      ,mth
      ,1 + wct.Modulo(
      (2 - DATEPART(DW,wct.DATEFLOAT(2011,mth,1)))
      ,7)
      ) as datetime) as [First Monday]
FROM (
      VALUES
            (1),(2),(3),(4),(5),(6),
            (7),(8),(9),(10),(11),(12)
            ) m(mth)
This produces the following result.
First Monday
-----------------------
2011-01-03 00:00:00.000
2011-02-07 00:00:00.000
2011-03-07 00:00:00.000
2011-04-04 00:00:00.000
2011-05-02 00:00:00.000
2011-06-06 00:00:00.000
2011-07-04 00:00:00.000
2011-08-01 00:00:00.000
2011-09-05 00:00:00.000
2011-10-03 00:00:00.000
2011-11-07 00:00:00.000
2011-12-05 00:00:00.000
 
(12 row(s) affected)


DATEFLOAT can simplify date calculations on the database. Let’s say that you are a company that has a 52-53 week fiscal year, and the year end is defined as the final Saturday in the year end month. You need to calculate the year end date and the number of days in the year.
DECLARE @YEM as int = 8
SELECT yr
,CAST([Last Saturday] as datetime) as [Last Saturday]
,[Last Saturday] - [Last Saturday PY] as [Days in Year]
FROM(
      SELECT yr
,wct.DATEFLOAT(yr, @YEM,DATEPART(d,wct.DATEFLOAT(yr, @YEM+1, 1)-7) + wct.Modulo((7 - DATEPART(DW,wct.DATEFLOAT(yr, @YEM+1, 1)-7)),7)) as [Last Saturday]
,wct.DATEFLOAT(yr-1, @YEM,DATEPART(d,wct.DATEFLOAT(yr-1, @YEM+1, 1)-7) + wct.Modulo((7 - DATEPART(DW,wct.DATEFLOAT(yr-1, @YEM+1, 1)-7)),7)) as [Last Saturday PY]
      FROM (
            VALUES
                  (2005),(2006),(2007),(2008),(2009),
                  (2010),(2011),(2012),(2013),(2014),
                  (2015),(2016),(2017),(2018),(2019)
                  ) n(yr)
            ) m
This produces the following result
        yr Last Saturday           Days in Year
----------- ----------------------- ------------
       2005 2005-08-27 00:00:00.000          364
       2006 2006-08-26 00:00:00.000          364
       2007 2007-08-25 00:00:00.000          364
       2008 2008-08-30 00:00:00.000          371
       2009 2009-08-29 00:00:00.000          364
       2010 2010-08-28 00:00:00.000          364
       2011 2011-08-27 00:00:00.000          364
       2012 2012-08-25 00:00:00.000          364
       2013 2013-08-31 00:00:00.000          371
       2014 2014-08-30 00:00:00.000          364
       2015 2015-08-29 00:00:00.000          364
       2016 2016-08-27 00:00:00.000          364
       2017 2017-08-26 00:00:00.000          364
       2018 2018-08-25 00:00:00.000          364
       2019 2019-08-31 00:00:00.000          371
 
(15 row(s) affected)


In this example the year end date is the Saturday that falls closest to the last day of the fiscal year end month.
DECLARE @YEM as int = 8
SELECT yr
,CAST(YE as datetime) as [YE Date]
,YE - PYE as [Days in Year]
FROM (
      SELECT yr
,wct.DATEFLOAT(yr, @YEM,DATEPART(d,wct.DATEFLOAT(yr, @YEM+1, 1)-4) + wct.Modulo((7 - DATEPART(DW,wct.DATEFLOAT(yr, @YEM+1, 1)-4)),7)) as YE
,wct.DATEFLOAT(yr-1, @YEM,DATEPART(d,wct.DATEFLOAT(yr-1, @YEM+1, 1)-4) + wct.Modulo((7 - DATEPART(DW,wct.DATEFLOAT(yr-1, @YEM+1, 1)-4)),7)) as PYE
      FROM (
            VALUES
                  (2005),(2006),(2007),(2008),(2009),
                  (2010),(2011),(2012),(2013),(2014),
                  (2015),(2016),(2017),(2018),(2019)
                  ) n(yr)
            ) m
This produces the following result.
         yr YE Date                 Days in Year
----------- ----------------------- ------------
       2005 2005-09-03 00:00:00.000          371
       2006 2006-09-02 00:00:00.000          364
       2007 2007-09-01 00:00:00.000          364
       2008 2008-08-30 00:00:00.000          364
       2009 2009-08-29 00:00:00.000          364
       2010 2010-08-28 00:00:00.000          364
       2011 2011-09-03 00:00:00.000          371
       2012 2012-09-01 00:00:00.000          364
       2013 2013-08-31 00:00:00.000          364
       2014 2014-08-30 00:00:00.000          364
       2015 2015-08-29 00:00:00.000          364
       2016 2016-09-03 00:00:00.000          371
       2017 2017-09-02 00:00:00.000          364
       2018 2018-09-01 00:00:00.000          364
       2019 2019-08-31 00:00:00.000          364
 
(15 row(s) affected)


Copyright 2008-2024 Westclintech LLC         Privacy Policy        Terms of Service