February 15, 2019 at 4:30 pm
I have a field that is formatted as yyyymm and need to use a dateadd on it to minus 2 months but I keep getting this error.
Msg 242, Level 16, State 3, Line 1
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
Select
DATEADD(MONTH, -2, FORMAT(GetDate(),'yyyyMM'))
How do I query this as a date?
Thanks.
February 15, 2019 at 4:40 pm
This returns the first of the current month
SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS StartOfMonth
So you'd just add months to that.
February 15, 2019 at 5:00 pm
Interesting, when I try to do a -2 months to this I get.
2019-03-01 00:00:00.000
SELECT DATEADD(month, DATEDIFF(month, -2, GETDATE()), 0) AS StartOfMonth
February 15, 2019 at 5:12 pm
sean.r.mason - Friday, February 15, 2019 4:30 PMI have a field that is formatted as yyyymm and need to use a dateadd on it to minus 2 months but I keep getting this error.Msg 242, Level 16, State 3, Line 1
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
Select
DATEADD(MONTH, -2, FORMAT(GetDate(),'yyyyMM'))How do I query this as a date?
Thanks.
If you want to format of those results as yyyyMM, format the results of the dateadd. Your query would be like: Select
FORMAT(DATEADD(MONTH, -2, GetDate()), 'yyyyMM')
Sue
February 15, 2019 at 5:18 pm
The data comes to me in this format 'yyyyMM'. What I need to do is query this field but with a dataadd -2 months. I'm using this in an SSIS package so I'm trying to prevent me from having to open the package everytime, edit the date, then run the package.
February 15, 2019 at 5:33 pm
sean.r.mason - Friday, February 15, 2019 5:18 PMThe data comes to me in this format 'yyyyMM'. What I need to do is query this field but with a dataadd -2 months. I'm using this in an SSIS package so I'm trying to prevent me from having to open the package everytime, edit the date, then run the package.
Something like this is get 201812 which seems like that would be today - two months.
So somewhere I am totally missing it - can you explain it to me with this data: CREATE TABLE SomeDateFormat
(SomeDate int not null)
INSERT SomeDateFormat
VALUES
(201801),
(201901),
(201902),
(201812),
(201811)
SELECT *
FROM SomeDateFormat
WHERE SomeDate = FORMAT(DATEADD(MONTH, -2, GetDate()), 'yyyyMM')
Sue
February 15, 2019 at 6:00 pm
This worked Perfect thank you very much.
Where disch_yyyymm = FORMAT(DATEADD(MONTH, -2, GetDate()), 'yyyyMM');
February 16, 2019 at 2:39 am
sean.r.mason - Friday, February 15, 2019 6:00 PMThis worked Perfect thank you very much.Where disch_yyyymm = FORMAT(DATEADD(MONTH, -2, GetDate()), 'yyyyMM');
DO NOT use the FORMAT function for this, it is very slow and the output is NVARCHAR(4000), not a good match for an integer column!
😎
There are much better ways of doing this calculation, here is one exampleSELECT (YEAR(DATEADD(MONTH,-2,GETDATE())) * 100) + MONTH(DATEADD(MONTH,-2,GETDATE()))
And anotherSELECT CONVERT(INT,CONVERT(VARCHAR(6),DATEADD(MONTH, -2, GetDate()),112),0)
February 16, 2019 at 7:48 am
Just to underline the bad performance of the FORMAT function, here is a simple test set
😎
USE TEEST;
GO
SET NOCOUNT ON;
--https://www.sqlservercentral.com/Forums/2020415/DateAdd-select-from-format-yyyymm-gets-error
--/* -- UNCOMMENT THIS LINE TO SKIP THE TEST DATASET CREATION
IF OBJECT_ID(N'dbo.TBL_TEST_DATE_FORMAT') IS NOT NULL DROP TABLE dbo.TBL_TEST_DATE_FORMAT;
CREATE TABLE dbo.TBL_TEST_DATE_FORMAT
(
TDF_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_TEST_DATE_FORMAT_TDF_ID PRIMARY KEY CLUSTERED
,TDF_DATE DATETIME NOT NULL
,TDF_INT INT NOT NULL
);
DECLARE @TEST_SIZE INT = 1000000;
;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) X(N))
, NUMS(N) AS (SELECT TOP (@TEST_SIZE) ROW_NUMBER() OVER (ORDER BY @@VERSION) AS N FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
,TDFSET(TDATE) AS
(
SELECT
DATEADD(DAY,(ABS(CHECKSUM(NEWID())) % 43510),CONVERT(DATETIME,0,0))
FROM NUMS NM
)
INSERT INTO dbo.TBL_TEST_DATE_FORMAT WITH (TABLOCKX) (TDF_DATE,TDF_INT)
SELECT
TFS.TDATE
,CONVERT(INT,CONVERT(VARCHAR(6),DATEADD(MONTH, -2, TFS.TDATE),112),0)
FROM TDFSET TFS
OPTION (MAXDOP 1);
CREATE NONCLUSTERED INDEX NCLIDX_DBO_TBL_TEST_DATE_FORMAT_TDF_INT ON dbo.TBL_TEST_DATE_FORMAT(TDF_INT ASC);
-- */
IF EXISTS (SELECT * FROM sys.indexes WHERE name = (N'NCLIDX_DBO_TBL_TEST_DATE_FORMAT_TDF_INT')) DROP INDEX NCLIDX_DBO_TBL_TEST_DATE_FORMAT_TDF_INT ON dbo.TBL_TEST_DATE_FORMAT;
DECLARE @DTBUCKET DATETIME = CONVERT(DATETIME,0,0);
DECLARE @INTBUCKET DATETIME = 0;
DECLARE @TESTDATE DATETIME = CONVERT(DATETIME,'19700101',112)
DECLARE @timer TABLE (T_TXT VARCHAR(50) NOT NULL, T_TD DATETIME2(7) NOT NULL DEFAULT (SYSDATETIME()));
INSERT INTO @timer(T_TXT) VALUES ('DRY RUN DATE');
SELECT
@DTBUCKET = TDF.TDF_DATE
FROM dbo.TBL_TEST_DATE_FORMAT TDF
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('DRY RUN DATE');
INSERT INTO @timer(T_TXT) VALUES ('DRY RUN DATE');
SELECT
@DTBUCKET = TDF.TDF_DATE
FROM dbo.TBL_TEST_DATE_FORMAT TDF
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('DRY RUN DATE');
INSERT INTO @timer(T_TXT) VALUES ('FORMAT DATE');
SELECT
@INTBUCKET = CONVERT(INT,FORMAT(DATEADD(MONTH, -2, TDF.TDF_DATE), 'yyyyMM'),0)
FROM dbo.TBL_TEST_DATE_FORMAT TDF
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('FORMAT DATE');
INSERT INTO @timer(T_TXT) VALUES ('YEAR_MONTH DATE');
SELECT
@INTBUCKET = (YEAR(DATEADD(MONTH,-2,TDF.TDF_DATE)) * 100) + MONTH(DATEADD(MONTH,-2,TDF.TDF_DATE))
FROM dbo.TBL_TEST_DATE_FORMAT TDF
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('YEAR_MONTH DATE');
INSERT INTO @timer(T_TXT) VALUES ('CONVERT_CONVERT DATE');
SELECT
@INTBUCKET = CONVERT(INT,CONVERT(VARCHAR(6),DATEADD(MONTH, -2, TDF.TDF_DATE),112),0)
FROM dbo.TBL_TEST_DATE_FORMAT TDF
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('CONVERT_CONVERT DATE');
INSERT INTO @timer(T_TXT) VALUES ('DRY RUN INT');
SELECT
@INTBUCKET = TDF.TDF_INT
FROM dbo.TBL_TEST_DATE_FORMAT TDF
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('DRY RUN INT');
INSERT INTO @timer(T_TXT) VALUES ('DRY RUN INT');
SELECT
@INTBUCKET = TDF.TDF_INT
FROM dbo.TBL_TEST_DATE_FORMAT TDF
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('DRY RUN INT');
INSERT INTO @timer(T_TXT) VALUES ('FORMAT PREDICATE');
SELECT
@INTBUCKET = TDF.TDF_INT
FROM dbo.TBL_TEST_DATE_FORMAT TDF
WHERE TDF.TDF_INT = CONVERT(INT,FORMAT(DATEADD(MONTH, -2, @TESTDATE), 'yyyyMM'),0)
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('FORMAT PREDICATE');
INSERT INTO @timer(T_TXT) VALUES ('YEAR_MONTH PREDICATE');
SELECT
@INTBUCKET = TDF.TDF_INT
FROM dbo.TBL_TEST_DATE_FORMAT TDF
WHERE TDF.TDF_INT = (YEAR(DATEADD(MONTH,-2,@TESTDATE)) * 100) + MONTH(DATEADD(MONTH,-2,@TESTDATE))
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('YEAR_MONTH PREDICATE');
INSERT INTO @timer(T_TXT) VALUES ('FORMAT PREDICATE');
SELECT
@INTBUCKET = TDF.TDF_INT
FROM dbo.TBL_TEST_DATE_FORMAT TDF
WHERE TDF.TDF_INT = CONVERT(VARCHAR(6),DATEADD(MONTH, -2, @TESTDATE),112)
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('FORMAT PREDICATE');
INSERT INTO @timer(T_TXT) VALUES ('CREATE INDEX');
CREATE NONCLUSTERED INDEX NCLIDX_DBO_TBL_TEST_DATE_FORMAT_TDF_INT ON dbo.TBL_TEST_DATE_FORMAT(TDF_INT ASC);
INSERT INTO @timer(T_TXT) VALUES ('CREATE INDEX');
INSERT INTO @timer(T_TXT) VALUES ('DRY RUN INT INDEX');
SELECT
@INTBUCKET = TDF.TDF_INT
FROM dbo.TBL_TEST_DATE_FORMAT TDF
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('DRY RUN INT INDEX');
INSERT INTO @timer(T_TXT) VALUES ('DRY RUN INT INDEX');
SELECT
@INTBUCKET = TDF.TDF_INT
FROM dbo.TBL_TEST_DATE_FORMAT TDF
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('DRY RUN INT INDEX');
INSERT INTO @timer(T_TXT) VALUES ('FORMAT PREDICATE INDEX');
SELECT
@INTBUCKET = TDF.TDF_INT
FROM dbo.TBL_TEST_DATE_FORMAT TDF
WHERE TDF.TDF_INT = CONVERT(INT,FORMAT(DATEADD(MONTH, -2, @TESTDATE), 'yyyyMM'),0)
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('FORMAT PREDICATE INDEX');
INSERT INTO @timer(T_TXT) VALUES ('YEAR_MONTH PREDICATE INDEX');
SELECT
@INTBUCKET = TDF.TDF_INT
FROM dbo.TBL_TEST_DATE_FORMAT TDF
WHERE TDF.TDF_INT = (YEAR(DATEADD(MONTH,-2,@TESTDATE)) * 100) + MONTH(DATEADD(MONTH,-2,@TESTDATE))
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('YEAR_MONTH PREDICATE INDEX');
INSERT INTO @timer(T_TXT) VALUES ('CONVERT_CONVERT PREDICATE INDEX');
SELECT
@INTBUCKET = TDF.TDF_INT
FROM dbo.TBL_TEST_DATE_FORMAT TDF
WHERE TDF.TDF_INT = CONVERT(INT,CONVERT(VARCHAR(6),DATEADD(MONTH, -2, @TESTDATE),112),0)
OPTION (MAXDOP 1);
INSERT INTO @timer(T_TXT) VALUES ('CONVERT_CONVERT PREDICATE INDEX');
SELECT
T.T_TXT
,DATEDIFF(MICROSECOND,MIN(T.T_TD),MAX(T.T_TD)) AS DURATION
FROM @timer T
GROUP BY T.T_TXT
ORDER BY DURATION;
T_TXT DURATION
------------------------------- --------
CONVERT_CONVERT PREDICATE INDEX 1501
YEAR_MONTH PREDICATE INDEX 2501
FORMAT PREDICATE INDEX 3003
YEAR_MONTH PREDICATE 93567
FORMAT PREDICATE 265187
DRY RUN DATE 479341
DRY RUN INT INDEX 503858
DRY RUN INT 564900
CREATE INDEX 1002214
CONVERT_CONVERT DATE 1037240
YEAR_MONTH DATE 1365472
FORMAT DATE 32044416
March 1, 2019 at 6:32 am
sean.r.mason - Friday, February 15, 2019 4:30 PMI have a field that is formatted as yyyymm and need to use a dateadd on it to minus 2 months but I keep getting this error.Msg 242, Level 16, State 3, Line 1
The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.
Select
DATEADD(MONTH, -2, FORMAT(GetDate(),'yyyyMM'))How do I query this as a date?
Thanks.
This is why you should NEVER store a date as anything OTHER than a date. If you have multiple date values that are integers that need to be checked, your best bet is to only do the two month thing once:
DECLARE @TwoMonthsAgo AS date = DATEADD(month, -2, GETDATE());
DECLARE @DateCheckValue AS int = (YEAR(@TwoMonthsAgo)) * 100) + MONTH(@TwoMonthsAgo);
SELECT ST.*
FROM SomeTable AS ST
WHERE ST.Disch_yyyymm = @DateCheckValue;
That way, the calculation has zero chance of affecting the query performance and only takes place once, computing the integer value needed to compare to an integer value. If your value is actually a string, you would change the above to CONVERT the year and month values to character strings and you're done.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply