August 2, 2005 at 4:20 am
Hey everyone
This is a bit of a long one, so i appologise in advance. I have a spreadsheet (ms excel) connected to a SQL Server database, connected through a query using Data|Get External Data.
A button on the spreadsheet runs the following query......
SELECT CallLogRevenues.category, CallLogRevenues.enquiryDescription, CallLogRevenues.resolution, CallLogRevenues.startDateTime, CallLogRevenues.endDateTime, CallLogRevenues.totalDateDiff, CallLogRevenues.dateOnlyStart, CallLogRevenues.dateOnlyEnd
FROM IntForms.thePass.CallLogRevenues CallLogRevenues WHERE ((CallLogRevenues.dateOnlyStart)>=?) AND ((CallLogRevenues.dateOnlyEnd)<=?)
Basically this pops up an input box where you type in the start date in the format dd/mm/yyyy, press enter, a second input box pops up where you type in the end date dd/mm/yyyy.
If i type in for example 25/07/2005 for both values then all the data for those dates is shown. However if i type in 01/07/2005 and 31/07/2005 to get all values for July then i get a heap of values from june. Strange ehh.
I think the reason it's doing this is because dateOnlyEnd and dateOnlyStart are varchar in the SQL Server database. However, i cannot change the design to dateTime as it only takes 8 chars ddmmyyyy, whereas i need 10 chars dd/mm/yyyy.
Is there a way around this, perhaps i could put in CVDate into the SQL Query or something.
Can someone please help me
Thanks guys
Dave
August 2, 2005 at 5:35 am
Sounds like your diagnosis is correct - you need to be comparing dates with dates. Can you change the design on the SQL Server side? Either change the datatype of the SQL Server date fields to datetime, or create extra computed fields on the table that do the conversion for you and use those?
If not, do the conversion on the fly. Here's one way of converting ddmmyyyy varchar to datetime:
declare @ProperDate datetime, @CharDate varchar(8)
set @CharDate = '25032005'
set @properdate = cast(right(@CharDate, 4)+ '-' + substring(@CharDate, 3, 2) + '-' + left(@CharDate, 2) + ' 00:00' as datetime)
select @CharDate, @properdate
Regards
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 2, 2005 at 5:59 am
Phil
Thanks for getting back to me. What you said sounded pretty complicated, but i'll give it a go, should be quite interesting do work out something i've never done before.
If i get stuck i'll submit another post, that is, if you wouldn't mind helping.
So will the code you provided me with take out the slashes (/) to take the date down from 10 chars (dd/mm/yyyy) to 8 chars (ddmmyyyy)
Thanks
Dave
August 2, 2005 at 7:02 am
No problem helping - post away!
Maybe I missed what you are trying to do. The code I provided changes a character string from DDMMYYYY format into a format that can be CAST to a datetime datatype in SQL Server (YYYYMMDD TT:TT). Once you have your data in a datetime variable, you can do less than, greater than etc comparisons and it will all work as expected.
If all you want to do is to remove slashes from a string, use the REPLACE function, eg:
select replace('25/02/2005', '/', '')
Does this help?
Phil
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 2, 2005 at 7:51 am
Sorry was out at lunch
That does help, i'll have a look into it and see if i can work it out
Thanks
Dave
August 3, 2005 at 1:49 am
For resilience, I would suggest that you use CONVERT() rather than CAST(). CONVERT() allows you to specify the date format, otherwise you are reliant on the database setting or need to issue a SET DATEFORMAT at the start of your sp.
Remember that DateTime includes time! 30-06-2005 stops at the beginning of 30 June and so excludes the last day of the month. You can adjust for this by adding time to your string before converting it or apply DATEADD() aftwerwards.
August 3, 2005 at 5:24 am
August 3, 2005 at 8:32 am
actually i might have to rethink this whole thing altogether. I have a button search button, when i press it you type the start date in the first pop up box and the end date in the second popup box. The following code in Microsoft Query does this for me......
SELECT CallLogRevenuesBACKUP20050802.category, CallLogRevenuesBACKUP20050802.enquiryDescription, CallLogRevenuesBACKUP20050802.resolution, CallLogRevenuesBACKUP20050802.startDateTime, CallLogRevenuesBACKUP20050802.endDateTime, CallLogRevenuesBACKUP20050802.totalDateDiff, CallLogRevenuesBACKUP20050802.dateOnlyStart, CallLogRevenuesBACKUP20050802.dateOnlyEnd, CallLogRevenuesBACKUP20050802.MF_SR_ID
FROM myForms.myUser.CallLogRevenuesBACKUP20050802 CallLogRevenuesBACKUP20050802
WHERE ((CallLogRevenuesBACKUP20050802.dateOnlyStart)>=?) AND ((CallLogRevenuesBACKUP20050802.dateOnlyEnd)<=?)
Is there a method of editing this so that when i type in the dates (e.g. 03/08/2005) it will actually use wildcards e.g 03/08/2005*
I'm just going to have to think about this one step at a time i think
Thanks
Dave
August 3, 2005 at 8:42 am
Sorry, I've no experience of Microsift Query. I'm not sure why you are referring to wildcards. DateTimes cannot have them but you can do a wildcard search with the LIKE operator and % or use regular expressions.
For example, SELECT * FROM dbo.MyTable WHERE MyField LIKE '%av%' would match Dave, Avid and Savvy.
Incidentally, it's usually not a good idea to have anyone other than dbo owning objects.
August 3, 2005 at 9:01 am
how do i explain this one.
The fields startDateOnly and endDateOnly fields are varchar, so when i carryout a search it doesn't work properly. The problem is i cannot change this to dateTime format as it is in the format dd/mm/yyyy, and SQL Server doesn't allow this as one of its formats. So i was thinking of using another field in my database (a varchar in the format of dd/mm/yyyy hh:mm:ss), converting that field into dateTime format and then using wildcards so that the user just has to put in the date and not the time.
How does that sound
Thanks Stewart
Dave
p.s have to head off soon, but if i don't have time to check this post today, then i'll check it tomorrow
August 3, 2005 at 9:07 am
Phil's original response should do what you want, then. Just modify the input string into the required string format (ie with /s) and perform a simple search. You don't need to go near DateTimes.
As I said, I know nothing about MicroSoft Query, so perhaps your problem is in handling the parameters. I will have to leave that to someone else.
August 3, 2005 at 9:34 am
Following up on Stwart's comment regarding the time element of DateTime values, unless I actually NEED the time portion on a date value I always compare date values using one of the following code examples:
#1 - Compare date value in table to some hard-coded date value
... where DATEADD(d,DATEDIFF(d,0,MyDate),0) = '12/15/2004'
#2 - Find all records in TableA where TableA.MyDate = Today's Date (regardless of timestamp)
... where DATEADD(d,DATEDIFF(d,0,MyDate),0) = DATEADD(d,DATEDIFF(d,0,GetDate()),0)
Here is some more info I got from another forum that may be usefull in SQL datetime trickery:
-- Strip off hours.. or other work arounds..
SELECT .... WHERE CONVERT(varchar, @DateVal, 101) = '08/01/2003'
SELECT .... WHERE @DateVal BETWEEN '2003-08-01' AND '2003-08-01 23:59:59.997'
SELECT .... WHERE @DateVal >= '2003-08-01' AND DateVal < '2003-08-02'
SELECT .... WHERE CAST(FLOOR(CAST(@DateVal AS float)) AS datetime) = '8/1/2003'
SELECT CAST(FLOOR(CAST(getdate() AS float)) AS datetime)
select CAST(CAST(GetDate() AS INT) AS DATETIME)
-- this one should run faster...
select DATEADD(d,DATEDIFF(d,0,getdate()),0)
-- or convert to Text
SELECT CONVERT(Varchar(10),GetDate(),120)
SELECT CONVERT(Varchar(10),GetDate(),101)
-- Calculate lapsed Time ONLY.. no date..
SELECT TimeLapse = LTRIM(RTRIM(CAST(DATEDIFF(hh, DATEADD(d,DATEDIFF(d,0,getdate()),0), getdate()) AS char(10)))) + ':' +
CAST(DATEDIFF(mi, DATEADD(d,DATEDIFF(d,0,getdate()),0), getdate()) % 60 AS Char(2))
/*
Contributed by Kay-Ole Behrmann
All of the above work to get records on 8/1/2003 regardless of the time of day.
The first solution converts to varchar format "101", i.e. US-Format mm/dd/yyyy, and compares to a string.
Doing it this way however, chances are you forget leading zeros (8/1/2003) and get no results.
The second solution uses knowledge about the largest possible timepart in a datetime, wich is not a particularly
pretty way.
The third way is the classic one, using two date-only values with an implicit time of 00:00:00.0000 and forces
a "greater or equal" to midnight 8/1 and a "less than" midnight 8/2.
Answer #4 cuts off the time part of the datetime value by removing decimals from the converted float-value.
This makes use of the fact that conversion to a float stores the date in the integer-part and time in the decimals.
*/
-- Mark Gelatt EndOfMonth
DECLARE @Today SmallDateTime,
@BOM SmallDateTime,
@EOM SmallDateTime
SET @Today = getdate()
SET @BOM = CAST(FLOOR(CAST( -- Find the First Of Month
DateAdd(dd,-Day(@Today), -- SUBTRACT the Number Of Days from @Today..
@Today) + 1 -- Add 1 Day to the second DateAdd parameter, which is the DateValue to Subtract the DAYs from (the 1st DateAdd parameter) - which gives us the FIRST Day of the month for @Today
AS float)) AS datetime) -- Strip off time and set to Midnight and CAST as DateTime
SET @EOM = CAST(FLOOR(CAST( -- Find the End Of Month
DateAdd(dd,-Day(DateAdd(mm,1,@Today)), -- SUBTRACT the Number Of Days from @Today PLUS 1 Month
DateAdd(mm,1,@Today)) -- Add 1 Month to the second DateAdd parameter, which is the DateValue to Subtract the DAYs from (the 1st DateAdd parameter) - which gives us the LAST Day of the month for @Today
AS float)) AS datetime) -- Strip off time and set to Midnight and CAST as DateTime
SELECT Today = @Today,
FirstOfMonth = @BOM,
EndOfMonth = @EOM
August 3, 2005 at 10:00 am
August 4, 2005 at 4:32 am
I really can't get any of this to work.
Would it be possible to convert a varchar(50) field to a valid dateTime format.
This is the data that is in the startDateTime field....
08/06/2005 15:44:52
Does this sound do-able?
Thanks
Dave
August 4, 2005 at 4:55 am
i used the date you provided as below
select convert(datetime,convert(varchar(50),'08/06/2005 15:44:52',126))
and i got this
2005-08-06 15:44:52.000
isn't this what you want
if it is then you can use the format i posted earlier.but put a varchar(50) instead of varchar(12)
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply