SQL Date Manipulations: CONVERT, DATEADD, EOMonth & More
- Cortlandt _
Date manipulation refers to the process of altering, calculating, or formatting date and time values.
Â
Date Parsing
Extract date components (e.g., year, month, day) from strings or formatted dates.
Functions include CONVERT, YEAR MONTH, DAY, DATEPART.
DATEPART: Extracts a specific part of a date, such as the day, month, or year.
Microsoft documentation: https://learn.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-ver15
Date Parsing with Date Arithmetic
Extract date components (e.g., year, month, day) from strings or formatted dates and then perform operations like addition or subtraction on dates to calculate durations, intervals, or new date values.
EOMONTH – last day of the current month
DATEADD – adds a number (a signed integer) to a datepart of an input date, and returns a modified date/time value. Syntax:
DATEADD (datepart , number , date )Microsoft documentation: https://learn.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql?view=sql-server-ver15
Â
Related Pages
T-SQL Date Expressions – examples
– most date manipulations work on date-time values from GETDATE().
– How to configure the to process bad dates in date fields from Providex data sources. Uses
will convert data from Sage 100 Providex to a format usable on ETL+ data warehouse
Related Pages from Microsoft
DATEADD (datepart , number , date )
– includes CONVERT, CAST, TRY_CONVERT
TESTS
DATEADD( … ) Returns Data Type of date Argument
Syntax: DATEADD (datepart , number , date )
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
select DATEADD(Day, -1, GETDate()) as DateTime,
DATEADD(Day, -1, CONVERT(Date, GETDate())) as DateType
INTO aaatemp;|
Â