Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
WalkerDA
Yak Posting Veteran
61 Posts |
 Posted - 2004-07-15 : 19:10:54
|
| I am trying to get the data where my log_date (time) is between vanother time that really isn't in a datetime format (varchar(50)) and I'm not getting any resluts back at all. SELECT RT_RAW_ID, RT.LOG_DATE, RTRIM(LTRIM(RIGHT(RT.LOG_DATE, 8))), COMP_ID, FK_COMP_ID, WEEK_DAY, RTRIM(LTRIM(RIGHT(CONVERT(DATETIME, BEGIN_TIME), 8))), RTRIM(LTRIM(RIGHT(CONVERT(DATETIME, END_TIME), 8))) FROM rptRT_RAW RT JOIN rptCOMP COMP ON FK_DATA_SRC_ID = 2 AND RT.RT_NODE_CD = COMP.FK_SW_SITE_CD AND RT.RT_TYPE_CD = COMP.FK_TYPE_CD AND RT.TM_OFC_CD = COMP.NUM_TM AND COMP.ACT_FLAG = 1 JOIN rptHOUR OUR ON OUR.FK_COMP_ID = COMP.COMP_ID AND OUR.ACT_FLAG = 1 AND DATEPART(dw, RT.LOG_DATE) = OUR.WEEK_DAY/***** Here is the problem but it looks good to me *****/ AND RTRIM(LTRIM(RIGHT(RT.LOG_DATE, 7))) BETWEEN RTRIM(LTRIM(RIGHT(CONVERT(DATETIME, OUR.BEGIN_TIME), 7))) AND RTRIM(LTRIM(RIGHT(CONVERT(DATETIME, OUR.END_TIME), 7)))Here is a row from the table:3752 2004-07-13 08:00:00.000 8:00AM 1346 1346 3 8:00AM 5:30PMAs you can see the data is in the DB. What's wrong with my query?Derrick |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-16 : 00:51:42
|
| What's the raw data in RT.LOG_DATE, OUR.BEGIN_TIME and OUR.END_TIME?Is there a lot of data in these tables? If so it would be much better to tidy the data up (add some columns and process the "text" date into a new DATETIME column) rather than using all these TRIM and RIGHT/LEFT functions in your statements. Given clean data, and a decently optimised query (e.g. some indexes added if necessary) SQL is likely to take SIGNIFICANTLY less time than when it has to use these string manipulation functions - although if its only a few thousand rows, or the report is only run once and the data then thrown away, its probably not an issue.Kristen |
 |
|
|
WalkerDA
Yak Posting Veteran
61 Posts |
Posted - 2004-07-16 : 07:06:50
|
| There isn't much data in the one table where the hours are held and likely no spaces though the columns are varchar and spaces could exist. RT.LOG_DATE is a datetime data type where as OUR.BEGIN_TIME and OUR.END_TIME are varchar data types that holds times such as 8:15, 9:30 and so on. SO what I am doing is comparing the last 7 or so characters in log_date '7:00AM' and testing to see if it is between begin and end time of '8:00AM' and '5:30PM'.Or at least that is what I am trying to do. The top portion (selects) of the query is not exactly what I need, it was used as an example to see what the results were from the (from) section.Derrick |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-16 : 08:41:01
|
OK, so to answer my own question, your data can be represented by:DECLARE @BEGIN_TIME varchar(100), @END_TIME varchar(100), @LOG_DATE datetimeSELECT @BEGIN_TIME = '8:00AM', @END_TIME = '5:30PM', @LOG_DATE = CONVERT(datetime, '2004-07-13 08:00:00.000') Tell me if that does NOT represent your data pleaseSo, you are doing the equivalent of :RTRIM(LTRIM(RIGHT(@LOG_DATE, 7))) BETWEEN RTRIM(LTRIM(RIGHT(CONVERT(DATETIME, @BEGIN_TIME), 7))) AND RTRIM(LTRIM(RIGHT(CONVERT(DATETIME, @END_TIME), 7)))Lets check that out:SELECT RIGHT(@LOG_DATE, 7), LTRIM(RIGHT(@LOG_DATE, 7)), RTRIM(LTRIM(RIGHT(@LOG_DATE, 7)))so that gives us a string ...SELECT CONVERT(DATETIME, @BEGIN_TIME), RIGHT(CONVERT(DATETIME, @BEGIN_TIME), 7), LTRIM(RIGHT(CONVERT(DATETIME, @BEGIN_TIME), 7)), RTRIM(LTRIM(RIGHT(CONVERT(DATETIME, @BEGIN_TIME), 7))) and so does thatSELECT CONVERT(DATETIME, @END_TIME), RIGHT(CONVERT(DATETIME, @END_TIME), 7), LTRIM(RIGHT(CONVERT(DATETIME, @END_TIME), 7)), RTRIM(LTRIM(RIGHT(CONVERT(DATETIME, @END_TIME), 7))) and so does thatso we wind up with SELECT '8:00AM'BETWEEN '8:00AM'AND '5:30PM'But because '5:30PM', as a string, is less than '8:00AM' nothing is going to be between them.Kristen |
|
|
|
WalkerDA
Yak Posting Veteran
61 Posts |
Posted - 2004-07-16 : 10:02:27
|
| So what I think you are saying is because the data is a string it won't work..so what I did was move the convert(datetime) function to the beginning and wipe out all of the other string functions SQL looks at it (begin and endtime) as true datetime data types and from that I get a working query... Strangley enough the first RTRIM(LTRIM(RIGHT(RT.LOG_DATE, 7))) works and SQL understands it because it is already a true datetime value whereas simply converting the string values BETWEEN CONVERT(DATETIME, OUR.BEGIN_TIME) AND CONVERT(DATETIME, OUR.END_TIME) SQL knows to compare the last 7 digits of the time from LOG_DATE to the last 7 digits of the time from begin and end time..sounds confused well I am but it works... AND LTRIM(RIGHT(RT.LOG_DATE, 8)) BETWEEN CONVERT(DATETIME, OUR.BEGIN_TIME) AND CONVERT(DATETIME, OUR.END_TIME)If I knew that's all I had to do life would have been easy!Derrick |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-07-17 : 03:15:01
|
| That's it - you've got it!You probably ought to COVNERT the first bit to a datetime too - just to make it unambiguous. That will also have the side effect that if for any reason LTRIM(RIGHT(RT.LOG_DATE, 8)) is NOT a valid datetime [component] SQL will blow up, rahter than just silently treating that sub-clause as FALSE.Personally I think you would be better off if the LOG_DATE, BEGIN_TIME and END_TIME column datatypes were ACTUAL datetime, rather than VARCHAR. That would enforce proper data (you can't store "FRED" in a datetime column!!) and would mean that SQL would run this type of query much faster because it won't have to mess around with all the data conversion - which in turn means your current query will not be using indexes or any other useful performance tricks that SQL is rather good at.Your call on that bit though!Kristen |
|
|
|
|
|
|
|
|
Query Help