Link to home
Start Free TrialLog in
Avatar of Mark Wilson
Mark Wilson

asked on

Date Parameter Question

I am using the query below to look provide with data from yesterday, one compares a varchar date and one a datetime field.

declare @start varchar(20)
declare @start1 datetime

set @start = CONVERT(varchar(20), DATEADD(dd, -1, GETDATE()), 103)
set @start1 = CONVERT(char(10), GETDATE() -1, 101)

SELECT etc
FROM table
WHERE     (CONVERT(varchar(10), O.INFO5, 112)) = @start  

UNION ALL

SELECT etc
FRM table
WHERE      CONVERT(char(10), M.CREATED, 101) = @start1

I now want to change the query to the following for both @start and @start1.


If the date = the 1st month, i.e. 1st Marh, Ist April etc the return all data from the previous month, i.e. if the date = 1st March, then retunr the data
from the 1st - 29th Feb, if its the 1st April, then 1st to 31st of March and so on for every month.

If the date does not equal the 1st month, then just return data from the previous day, as is the case now.

Can anybody help with this?
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

which of the fields is datetime? and which is varchar? the varchar field, what does the data/date look like?
Avatar of Mark Wilson
Mark Wilson

ASKER

O.INFO5 is the varchar field, the data looks like '27/02/2008 18:03'
M.CREATED is the datetime field
here we go:
declare @start varchar(10)
declare @start1 datetime
 
set @start = CONVERT(varchar(10), DATEADD(dd, -1, GETDATE()), 103)
set @start1 = convert(datetime, CONVERT(char(10), GETDATE(),120), 120)
 
SELECT etc
FROM table
WHERE  O.INFO5 LIKE @start + '%' 
    OR  ( M.CREATED >= dateadd(day, -1, @start1)
      AND M.Created < @start1 
        ) 

Open in new window

Thanks for your help on this much appreciated

I am a beginner with this so forgive the question.

'If the date = the 1st month, i.e. 1st Marh, Ist April etc the return all data from the previous month'

What part of the query brings this back?

here we go:
declare @start varchar(10)
declare @start1 datetime
declare @end1 datetime
 
IF (datepart(day, getdate()) = 1
BEGIN 
  set @start = CONVERT(varchar(8), DATEADD(dd, -1, GETDATE()), 103)
  set @start1 = dateadd(month, -1, convert(datetime, CONVERT(char(10), GETDATE(),120), 120))
  set @end1 = convert(datetime, CONVERT(char(10), GETDATE(),120), 120)
END
ELSE
BEGIN
  set @start = CONVERT(varchar(10), DATEADD(dd, -1, GETDATE()), 103)
  set @start1 = convert(datetime, CONVERT(char(10), GETDATE(),120), 120)
  set @end1 = convert(datetime, CONVERT(char(10), GETDATE(),120), 120)
END
 
 
SELECT etc
FROM table
WHERE  O.INFO5 LIKE @start + '%' 
    OR  ( M.CREATED >= @start1
      AND M.Created < @end1
        ) 

Open in new window

Thanks.

M.created works fine now.

The proble is with o.info5, I have set (datepart(day, getdate()) to 10 to test, but o.info5 brings me back yesterdays data instead of the 1st to 29th Febs data.

>The proble is with o.info5, I have set (datepart(day, getdate()) to 10 to test, but o.info5 brings me back yesterdays data instead of the 1st to 29th Febs data.

my code will only return the previous month if it is currently the 1st of the month...

ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi,

I have set the following to 11 to test the last full month

IF (datepart(day, getdate()) = 11

I get the error message

Syntax error converting the varchar value '%/' to a column of data type int.

Cheers


I see.
change
  set '%/' + datepart(month, DATEADD(dd, -1, GETDATE())) + '/' + datepart(year, DATEADD(dd, -1, GETDATE())) + '%'


into:

  set '%/' + cast(datepart(month, DATEADD(dd, -1, GETDATE())) as varchar(5)) + '/' + cast(datepart(year, DATEADD(dd, -1, GETDATE())) as varchar(5)) + '%'