1

I want to fetch previous day record from the table using a date field in Sql Server.

However I'm using the below sql statements but it's not giving any record.

TDTE = CAST(DATEADD(DD,-1,CURRNT_TIMESTAMP) AS DECIMAL(8,0))

OR 

TDTE=CAST(DATEADD(DD,-1,GETDATE())AS DECIMAL(8,0))

where TDTE column is in YYYYMMDD format.

Loofer
  • 6,841
  • 9
  • 61
  • 102
Gyan05
  • 15
  • 2

2 Answers2

2

Remove the Cast function Dateadd returns Date not a Integer value. Try this.

Where TDTE  = Cast(DATEADD(DD,-1,GETDATE()) as Date)
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
  • It throws error.SqlState: 22003, ErrorCode: 8115, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type datetime. – Gyan05 Jan 12 '15 at 09:54
  • @Gyan05 Not sure why got that error check now updated – Pரதீப் Jan 12 '15 at 10:05
  • SQL##f - SqlState: 22005, ErrorCode: 206, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]Operand type clash: date is incompatible with decimal... Tried DECIMAL(8,0) in place of Date. but not getting any result also – Gyan05 Jan 12 '15 at 10:23
1

Try this.

Where TDTE  = CONVERT(VARCHAR(10), DATEADD(DAY,-1,GETDATE()), 112)

OR(in decimal format)

Where TDTE  = CAST(CONVERT(VARCHAR(10), DATEADD(DAY,-1,GETDATE()), 112)AS DECIMAL(18,0))
Sarath Subramanian
  • 20,027
  • 11
  • 82
  • 86
  • Excellent..Finally..:) Could you please tell why that 112 used? – Gyan05 Jan 12 '15 at 11:10
  • Its a **CONVERT(VARCHAR(10),GETDATE(),112)** will convert the date to **YYYYMMDD** format. Its already written by SQL Server. You can change to another format by changing number, for eg, **103** will convert date to **dd/mm/yyyy** format. @Gyan05 – Sarath Subramanian Jan 12 '15 at 11:13
  • Good one didn't thought about it +1 – Pரதீப் Jan 12 '15 at 11:35
  • You would have solved faster than me since u r good in queries than me. OP updated the type of column later. Anyway thank you :) @NoDisplayName – Sarath Subramanian Jan 12 '15 at 11:37
  • Hey friend, I have posted a question 2 days back http://stackoverflow.com/questions/27838045/difference-between-left-join-and-outer-apply . I didn't get a proper feedback with real time examples. Can u bring some real time examples and a good solution for my question? @NoDisplayName – Sarath Subramanian Jan 12 '15 at 11:38