SQL Date Manipulations: CONVERT, DATEADD, EOMonth & More
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
T-SQL GETDATE() Function – most date manipulations work on date-time values from GETDATE().
Sage 100 Providex Dates – How to configure the ETL+ Design Page to process bad dates in date fields from Providex data sources. Uses
ETL+ Design Page will convert data from Sage 100 Providex to a format usable on ETL+ data warehouse
Related Pages from Microsoft
DATEADD (datepart , number , date )https://learn.microsoft.com/en-us/sql/t-sql/functions/dateadd-transact-sql?view=sql-server-ver15
https://learn.microsoft.com/en-us/sql/t-sql/functions/conversion-functions-transact-sql?view=sql-server-ver15 – includes CONVERT, CAST, TRY_CONVERT
TESTS
DATEADD( … ) Returns Data Type of date Argument
Syntax: DATEADD (datepart , number , date )
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Query in SSMS
select DATEADD(Day, -1, GETDate()) as DateTime,
DATEADD(Day, -1, CONVERT(Date, GETDate())) as DateType
INTO aaatemp;|
Results (shown in Design pane)