SQL Server

Date Functions in SQL SERVER Part III || DATEPART, DATETIME2FROMPARTS, DATETIMEFROMPARTS and EOMONTH

• Report rights infringement
• published: 12 Oct 2016
• views: 2

This video will provide you the details of each Date and Time Data Types and Function in SQL Server 2014. Last session for date other date functions: Part I https://www.youtube.com/watch?v=2x29rH6nY3s Part II https://www.youtube.com/watch?v=A0NM91RIaLI *************************************************************************  DATEPART (Transact-SQL) Returns an integer that represents the specified datepart of the specified date.  Syntax DATEPART ( datepart , date )  Arguments datepart Is the part of date (a date or time value) for which an integer will be returned. The following table lists all valid datepart arguments. User-defined variable equivalents are not valid. date Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable, or string literal. To avoid ambiguity, use four-digit years.  Return Type Int *************************************************************************  DATETIME2FROMPARTS (Transact-SQL) Returns a datetime2 value for the specified date and time and with the specified precision.  Syntax DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )  Arguments year Integer expression specifying a year. month Integer expression specifying a month. Softtechforum day Integer expression specifying a day. hour Integer expression specifying hours. minute Integer expression specifying minutes. seconds Integer expression specifying seconds. fractions Integer expression specifying fractions. precision Integer literal specifying the precision of the datetime2 value to be returned.  Return Types datetime2( precision )  Examples • Example without fractions of a second SELECT DATETIME2FROMPARTS ( 2016, 12, 31, 23, 59, 59, 0, 0 ) AS Result; • Example with fractions of a second The following example demonstrates the use of the fractions and precision parameters: 1. When fractions has a value of 5 and precision has a value of 1, then the value of fractions represents 5/10 of a second. 2. When fractions has a value of 50 and precision has a value of 2, then the value of fractions represents 50/100 of a second. 3. When fractions has a value of 500 and precision has a value of 3, then the value of fractions represents 500/1000 of a second. Transact-SQL SELECT DATETIME2FROMPARTS (2016, 8, 15, 14, 23, 44, 5, 1 ); SELECT DATETIME2FROMPARTS (2016, 8, 15, 14, 23, 44, 50, 2 ); SELECT DATETIME2FROMPARTS (2016, 8, 15, 14, 23, 44, 500, 3 ); *************************************************************************  DATETIMEFROMPARTS (Transact-SQL) Returns a datetime value for the specified date and time.  Syntax DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )  Arguments year Integer expression specifying a year. month Integer expression specifying a month. day Integer expression specifying a day. hour Integer expression specifying hours. minute Integer expression specifying minutes. seconds Integer expression specifying seconds. milliseconds Integer expression specifying milliseconds.  Return Types datetime  Examples SELECT DATETIMEFROMPARTS ( 2016, 12, 31, 23, 59, 59, 0 ) AS Result; *************************************************************************  DAY/YEAR/MONTH (Transact-SQL) Returns an integer representing the day (day of the month)/year/month of the specified date.  Syntax DAY ( date ) / YEAR ( date ) / MONTH ( date )  Arguments date Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. The date argument can be an expression, column expression, user-defined variable or string literal.  Return Type Int  Return Value DAY returns the same value as DATEPART (day, date). Same with DATEPART(month,date) and DATEPART(year,date) If date contains only a time part, the return value is 1, the base day. *************************************************************************  EOMONTH (Transact-SQL) Returns the last day of the month that contains the specified date, with an optional offset.  Syntax EOMONTH ( start_date [, month_to_add ] )  Arguments start_date Date expression specifying the date for which to return the last day of the month. month_to_add Optional integer expression specifying the number of months to add to start_date. If this argument is specified, then EOMONTH adds the specified number of months to start_date, and then returns the last day of the month for the resulting date. If this addition overflows the valid range of dates, then an error is raised.  Return Type date