compare getdate
-
Hi
Im trying to compare the getdate function with a column of the same type they work and returns data if i use less than < but when i want to cut out the time portion they will not compare it returns nothing.
Any suggestions?
Here is a snip?
SELECT dbo.DEC_TXN.*
FROM dbo.DEC_TXN INNER JOIN
dbo.CALENDAR ON dbo.DEC_TXN.DATE_LOAD = dbo.CALENDAR.dt
WHERE REPLACE(LEFT(Convert(varchar(10),dbo.DEC_TXN.DATE_LOAD,120),10),'-','')= REPLACE(LEFT(Convert(varchar(10),getdate(),120),10),'-','')
-
the way you had it would execute inefficiently and not use indexes.
Try this way.
SELECT dbo.DEC_TXN.*
FROM dbo.DEC_TXN
INNER JOIN dbo.CALENDAR ON dbo.DEC_TXN.DATE_LOAD = dbo.CALENDAR.dt
where dbo.DEC_TXN.DATE_LOAD between convert(datetime, convert(varchar(10),dateadd(dd,-1,getdate(),101) + '07:00:00')) and getdate()
return all records between yesterday at 7:00 am, and right now.
-
SELECT dbo.DEC_TXN.*
FROM dbo.DEC_TXN
INNER JOIN dbo.CALENDAR ON dbo.DEC_TXN.DATE_LOAD = dbo.CALENDAR.dt
where dbo.DEC_TXN.DATE_LOAD between convert(datetime, convert(varchar(10),dateadd(dd,-1,getdate(),101) + '07:00:00')) and getdate()
Server: Msg 174, Level 15, State 1, Line 4
The dateadd function requires 3 arguments.
Even though it has three arguments.
-
I think a bracket has been misplaced somehow, you had four arguments in DateAdd(). I also added a space at the front of the ' 07:00:00'
...
convert(datetime,convert(varchar(10),dateadd(dd,-1,getdate()),101)+ ' 07:00:00')
David
If it ain't broke, don't fix it...
-
What doesn't have 3 arguments in the code above is the outer "convert" function. Also, I was thinking that I'd be surprised if the INNER JOIN would return many (or any) rows as my guess is that the date in the CALENDAR table has a time of 00:00:00.000 whereas the DATE_LOAD will probably have some time value. Do you need to join to this CALENDAR table? It appears no columns are being used from it.
The manner I would try for this query is:
SELECT dbo.DEC_TXN.* FROM dbo.DEC_TXN WHERE dbo.DEC_TXN.DATE_LOAD >= dateadd(hh,-17, convert(datetime, cast(month(getdate()) as char(2)) + '/' + cast(day(getdate()) as char(2)) + '/' + cast(year(getdate()) as char(4)),101))
This should return all DEC_TXN records with a DATE_LOAD datetime greater than or equal to yesterday at 7:00am.
-
I have created the Calendar table so as to display all public holidays
Snip
dt isWeekday isHoliday Y FY Q M D DW monthname dayname W HolidayDescription
------------------------------------------------------ --------- --------- ------ ------ ---- ---- ---- ---- --------- --------- ---- --------------------------------
2000-01-02 00:00:00 0 0 2000 2000 1 1 2 1 January Sunday 2 NULL
2000-01-03 00:00:00 1 0 2000 2000 1 1 3 2 January Monday 2 NULL
2000-01-04 00:00:00 1 0 2000 2000 1 1 4 3 January Tuesday 2 NULL
2000-01-05 00:00:00 1 0 2000 2000 1 1 5 4 January Wednesday 2 NULL
2000-01-06 00:00:00 1 0 2000 2000 1 1 6 5 January Thursday 2 NULL
2000-01-07 00:00:00 1 0 2000 2000 1 1 7 6 January Friday 2 NULL
But in the first scenario:
SELECT dbo.DEC_TXN.*
FROM dbo.DEC_TXN INNER JOIN
dbo.CALENDAR ON dbo.DEC_TXN.DATE_LOAD = dbo.CALENDAR.dt
WHERE REPLACE(LEFT(Convert(varchar(10),dbo.DEC_TXN.DATE_LOAD,120),10),'-','')= REPLACE(LEFT(Convert(varchar(10),getdate(),120),10),'-','')
I wanted to match todays date with any records in the dec_txn table... before i could check if it was a public holiday or weekend. Nothing Macthed which didn make sense.
In the normal week days it needs to return everything from yest 7 am till today whenever the query is run hence getdate().
If it were a weekend i need to return everything since the last working today. My Calendar table works 100%
Eg. Easter weekend would return all records from dec_txn everything since Easter Friday up until the day after Easter Monday including that tuesday.
And this is y i need to use the inner join the date_load does use a time stamp.
This is what my query looks like so far and it still returns nothing:
SELECT dbo.DEC_TXN.DATE_LOAD,dbo.CALENDAR.dt
FROM dbo.DEC_TXN INNER JOIN
dbo.CALENDAR ON dbo.DEC_TXN.DATE_LOAD = dbo.CALENDAR.dt
WHERE (dbo.DEC_TXN.DATE_LOAD >= DATEADD(hh, - 17, CONVERT(datetime, CAST(MONTH(GETDATE()) AS char(2)) + '/' + CAST(DAY(GETDATE()) AS char(2))
+ '/' + CAST(YEAR(GETDATE()) AS char(4)), 101)))
any Suggestions?
-
Does DEC_TXN.DATE_LOAD have non-zero times? If so, your join will not return any rows, since in your example the Calendar table has zero times.
Also, you doing unnecessary work - use CONVERT style 112 (YYYYMMDD) instead of 125, and you won't need to do the REPLACE()
JOIN unchanged:
SELECT dbo.DEC_TXN.*
FROM dbo.DEC_TXN
INNER JOIN dbo.CALENDAR ON dbo.DEC_TXN.DATE_LOAD = dbo.CALENDAR.dt
WHERE Convert(varchar(8),dbo.DEC_TXN.DATE_LOAD,112) = Convert(varchar(8),getdate(),112)
JOIN changed:
SELECT dbo.DEC_TXN.*
FROM dbo.DEC_TXN
INNER JOIN dbo.CALENDAR
ON Convert(varchar(8), dbo.DEC_TXN.DATE_LOAD,112) = Convert(varchar(8),dbo.CALENDAR.dt,112)
WHERE Convert(varchar(8),dbo.DEC_TXN.DATE_LOAD,112) = Convert(varchar(8),getdate(),112)
-
It still doesnt match anything even though i try
JOIN changed:
SELECT dbo.DEC_TXN.*
FROM dbo.DEC_TXN
INNER JOIN dbo.CALENDAR
ON Convert(varchar(8), dbo.DEC_TXN.DATE_LOAD,112) = Convert(varchar(8),dbo.CALENDAR.dt,112)
WHERE Convert(varchar(8),dbo.DEC_TXN.DATE_LOAD,112) = Convert(varchar(8),getdate(),112)
Yes my Dec_txn Table does have 00:00:00 items for time, but i have changed the DEC_TXN.DATE_LOAD field to use times for the current year and this is what i need to match on. First i need to find a macth on a date then the time portion. I dont understand because it does not wana macth even on the date portion alone.
With the folling queries it returns dates in this form:
SELECT Convert(varchar(8),getdate(),112)AS 'Getdate'
SELECT Convert(varchar(8),dbo.CALENDAR.dt,112) As 'Calendar'
FROM dbo.CALENDAR
SELECT Convert(varchar(8), dbo.DEC_TXN.DATE_LOAD,112) As 'DEC_TXN'
FROM dbo.DEC_TXN
Getdate
--------
20060413
Calendar
--------
20000102
20000103
DEC_TXN
--------
20060227
20060227
and when i do a join it still returns nothing.
-
It must be a data issue, such as:
1. The Calendar table doesn't include today's date
2. The JOIN alone doesn't return any data, in which case the Calendar table once again doesn't include dates in the DEC_TXN table.
Without seeing all the data, I can't say for sure. In these types of situations, I very closely analyze all of the data in all of the applicable tables. It's got to be a data issue, because the query looks good to me.
Of course, unless you are using other columns from the Calendar table that are not shown here, why not just use:
SELECT dbo.DEC_TXN.*
FROM dbo.DEC_TXN
WHERE Convert(varchar(8), dbo.DEC_TXN.DATE_LOAD, 112) = Convert(varchar(8), GetDAte(), 112)
Back to the original question, what do you get when you execute this query:
SELECT Convert(varchar(8), dbo.DEC_TXN.DATE_LOAD,112) AS LoadDate
, Convert(varchar(8),dbo.CALENDAR.dt,112) AS CalDate
FROM dbo.DEC_TXN
INNER JOIN dbo.CALENDAR
ON Convert(varchar(8), dbo.DEC_TXN.DATE_LOAD,112) = Convert(varchar(8),dbo.CALENDAR.dt,112)
and this query:
SELECT Convert(varchar(8), dbo.DEC_TXN.DATE_LOAD,112) AS LoadDate
, Convert(varchar(8),dbo.CALENDAR.dt,112) AS CalDate
FROM dbo.DEC_TXN
LEFT JOIN dbo.CALENDAR
ON Convert(varchar(8), dbo.DEC_TXN.DATE_LOAD,112) = Convert(varchar(8),dbo.CALENDAR.dt,112)
-
Thanks all, its seems that i had to use a little discretion from everyone who pitched in something, and come up with the best solution.
My final query looks like this:
SELECT COUNT(*) AS [COUNT], dbo.DEC_TXN.CUSTOMER, dbo.DEC_TXN.STATUS, dbo.STATUS.STATUS_DESC
FROM dbo.DEC_TXN INNER JOIN
dbo.CALENDAR ON CONVERT(varchar(8), dbo.DEC_TXN.DATE_LOAD, 112) = CONVERT(varchar(8), dbo.CALENDAR.dt, 112) INNER JOIN
dbo.STATUS ON dbo.DEC_TXN.STATUS = dbo.STATUS.STATUS
WHERE (dbo.DEC_TXN.DATE_LOAD >= DATEADD(hh, - 17, CONVERT(datetime, CAST(MONTH(GETDATE()) AS char(2)) + '/' + CAST(DAY(GETDATE()) AS char(2))
+ '/' + CAST(YEAR(GETDATE()) AS char(4)), 101)))
GROUP BY dbo.DEC_TXN.CUSTOMER, dbo.DEC_TXN.STATUS , dbo.STATUS.STATUS_DESC
ORDER by dbo.DEC_TXN.CUSTOMER, dbo.DEC_TXN.STATUS
and it works i just need to add a filter to include the public holidays and weekends now..
Once again thanks to all
-
Hi Kazo,
To get rid of the time part in GetDate() i use the following statement:
select cast(floor(cast(GetDate() as float)) as datetime)
The result for this would be:
2006-04-13 00:00:00.000
And you should be able to do normal date comparison with a column that does not include time.
Hope this helps
Pieter
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply