CODEWITHSUNDEEP
sql-servert-sql
Khatra
Khatra

Reputation: 39

how to retrieve the records based on the GETDATE

I am trying to automate the task using SQL server agent to generate the report of the people who gets inserted to the table on regular basis. I have created the below stored procedure, and was trying to use same query in SQL server agent job but is not working can someone please help.

SELECT s.LAST_NAME AS sn,
RTRIM(s.FIRST_NAME)+ ' ' + LTRIM(s.LAST_NAME) AS Name,
s.FIRST_NAME AS F_Name
LEFT(middle_name,1) AS Initial,
sy.USERNAME AS USER,
s.HOME_ZIP AS ZIP,
RTRIM(UPPER(sy.USERNAME)) + LTRIM('@xyz.com') AS userP,
stm.DESCRIPTION_Maj AS company,
rg.RECORD_INPUT_DATE
FROM STCIO s
JOIN SYSME sy
ON s.ID_NUMBER =sy.ID_NUMBER
JOIN EHMGR rg
ON s.ID_NUMBER =rg.ID_NUMBER
JOIN STMEER stm
ON rg.MAJOR =stm.MAJOR
AND s.MAT_CODE IN ('','G','Q')
AND rg.CURRENT_FLAG = 'X'
AND CONVERT(datetime,CONVERT(CHAR(8),rg.RECORD_INPUT_DATE)) = GETDATE()

NOTE:datatype for Record_input_date is numeric(8,0)

Error message received is

"Arithmetic overflow error converting expression to data type datetime."

I don't have an authority to make any changes. All I'm looking for is to have this query running converting the record_input_date (numeric) to datetime and populate the record based on the getdate()

Upvotes: 1

Views: 75

Answers (2)

Sean Lange
Sean Lange

Reputation: 33581

Step 1 is turning this wall of text query into something you can read.

SELECT s.LAST_NAME AS sn
    , RTRIM(s.FIRST_NAME) + ' ' + LTRIM(s.LAST_NAME) AS Name
    , s.FIRST_NAME AS F_Name
    , LEFT(middle_name, 1)AS Initial
    , sy.USERNAME AS [USER]
    , s.HOME_ZIP AS ZIP
    , RTRIM(UPPER(sy.USERNAME)) + '@xyz.com' AS userP
    , stm.DESCRIPTION_Maj AS company
    , rg.RECORD_INPUT_DATE
FROM STCIO s
JOIN SYSME sy ON s.ID_NUMBER = sy.ID_NUMBER
JOIN EHMGR rg ON s.ID_NUMBER = rg.ID_NUMBER
JOIN STMEER stm ON rg.MAJOR = stm.MAJOR 
    AND s.MAT_CODE in ('', 'G', 'Q')
    AND rg.CURRENT_FLAG = 'X'
    AND CONVERT(DATETIME, CONVERT(CHAR(8), rg.RECORD_INPUT_DATE)) = GETDATE()

The problem here is that you have an integer that is not able to be converted to a datetime value. This is an inherent problem of using improper datatypes. You are likely going to be forced to drop the date condition from this query and replace it with an ISDATE. Insert those results to a temp table. Then another query to pull from the temp table with your date predicates.

Upvotes: 0

DhruvJoshi
DhruvJoshi

Reputation: 17126

Now this would happen if you still have the date stored as numeric in a wrong format (non ANSI format)

Like instead of 20160307 for today's date it stores it as 20160703 in which case it will give error for values like 20162002 or when the date is stored as ddmmyyyy or any other variant format. To solve look at some sample data and tweak your query from 

CONVERT(datetime,convert(char(8),rg.RECORD_INPUT_DATE)) = GETDATE()

to

  CONVERT(datetime,convert(char(8),rg.RECORD_INPUT_DATE),<formatstring>) = GETDATE()

See list of format strings here

Another way is to use date from parts function in higher version of sql server like

SELECT DATEFROMPARTS(RECORD_INPUT_DATE / 10000, 
                     RECORD_INPUT_DATE % 100,
                     (RECORD_INPUT_DATE/ 100) % 100) AS YR_MNTH_DT

If you cannot use either of above, you'll have to isolate days,months and year from the number. Example if your number is wrong format like ddmmyyyy (03062016)

DECLARE @dd INT, @mm INT, @yyyy INT, @newdate INT
SET @dd= RECORD_INPUT_DATE/1000000 --3
SET @mm= (RECORD_INPUT_DATE/10000) %100--6
SET @yyyy= (RECORD_INPUT_DATE) % 10000--2016
SET @newdate= @yyyy*10000+@mm*100+@dd

and use this @newdate for comparison

CONVERT(datetime,convert(char(8),@newdate)) = GETDATE()

Upvotes: 1

Related Questions