March 5, 2010 at 6:56 pm
Thanks a lot Jeff,
All I am trying clear up (for my current and fututre benefit) is that in a situation such as mine, where a fieldname is of DateTime datatype, but is only given a date value, eg, 3/4/2010, the value is supposed to be something like: 3/4/2010 00:00:00.
Given this scenario, which of Lutz's solution should I use,
This --> where functionDate > date + 1?
OR
This --> WHERE functionDateselect > dateadd(dd,datediff(dd,0,getdate()),0)
Lots of thanks again to all of you who have contributed to my thread.
March 5, 2010 at 7:15 pm
simflex-897410 (3/5/2010)
Thanks a lot Jeff,All I am trying clear up (for my current and fututre benefit) is that in a situation such as mine, where a fieldname is of DateTime datatype, but is only given a date value, eg, 3/4/2010, the value is supposed to be something like: 3/4/2010 00:00:00.
Given this scenario, which of Lutz's solution should I use,
This --> where functionDate > date + 1?
OR
This --> WHERE functionDateselect > dateadd(dd,datediff(dd,0,getdate()),0)
Lots of thanks again to all of you who have contributed to my thread.
Okay - in that scenario, let's take a look at what you want to do. You want all dates where the date is tomorrow or greater. So, we need to calculate the value to look for. To do that, we want to get tomorrow's date at midnight (e.g. 2010-03-06 00:00:00.000).
Using the following formula, we get today's date at midnight:
SELECT DATEADD(day, DATEDIFF(day, 0, getdate()), 0);
Now, to get tomorrows date at midnight we just need to add one more day:
SELECT DATEADD(day, DATEDIFF(day, 0, getdate()) + 1, 0);
Now, our query is going to use that as:
SELECT {column}
FROM {tables}
WHERE functionDate >= DATEADD(day, DATEDIFF(day, 0, getdate()) + 1, 0);
Now, if you do not have any time portion (all rows have 00:00:00.000 for the time) you could do the following:
SELECT {column}
FROM {tables}
WHERE functionDate > DATEADD(day, DATEDIFF(day, 0, getdate()), 0);
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 5, 2010 at 7:36 pm
WOW, I just love it when I see elaborate explanations such as yours.
Thanks a lot Jeff.
Your queries beg one question.
So, the only reason you used this -> getdate()) + 1 is just to handle the time portion, not necessarily to add one additional day to the date value?
March 6, 2010 at 9:26 am
simflex-897410 (3/5/2010)
WOW, I just love it when I see elaborate explanations such as yours.Thanks a lot Jeff.
Your queries beg one question.
So, the only reason you used this -> getdate()) + 1 is just to handle the time portion, not necessarily to add one additional day to the date value?
No, if you break the code down that '+ 1' is not on the getdate() function but rather on the result of the DATEDIFF. The DATEDIFF function returns the number of days between the 0 day and current day. We add one to get tomorrow - then we use the DATEADD function to add that number of days to the 0 date.
The 0 date is equivalent to putting in '1900-01-01 00:00:00.000', so we end up with a date and the time zeroed out.
We could rewrite that statement as:
DATEADD(day, DATEDIFF(day, '2010-01-01 00:00:00.000', getdate()) + 1, '2010-01-01 00:00:00.000')
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 6, 2010 at 10:28 am
One question is still unanswered as far as I'm concerned:
What is the data type of your functionDate column?
Please provide the related section from the DDL script (CREATE TABLE script).
Currently, we have three options:
DATE, DATETIME (or SMALLDATETIME) or some sort of CHAR() or VARCHAR().
You made two statements that simply don't match at all: data type is DATETIME vs. values are stored with date portion only.
May I ask again to clarify, please?
March 6, 2010 at 7:55 pm
hi Jeff and Lutz
Here is the DDL (the relevant info).
CREATE TABLE [dbo].[myTable] (
[Key] [int] NOT NULL ,
[functionDate] [datetime] NULL ,
[EventTime] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
The reason it appears that I am still confusing everyone is the statement that
1, FunctionDate is of DateTime datatype, and that
2, only date value is stored in it.
As you can see from the DDL statement, datat type is of dateTime variety.
However, during data entry though, only date as in:
insert into mytable (funtionDate) values('3/6/2010')
No time; only date.
That's what I mean.
Sorry again, about the confusion.
March 7, 2010 at 1:54 am
simflex-897410 (3/6/2010)
hi Jeff and LutzHere is the DDL (the relevant info).
CREATE TABLE [dbo].[myTable] (
[Key] [int] NOT NULL ,
[functionDate] [datetime] NULL ,
[EventTime] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
The reason it appears that I am still confusing everyone is the statement that
1, FunctionDate is of DateTime datatype, and that
2, only date value is stored in it.
As you can see from the DDL statement, datat type is of dateTime variety.
However, during data entry though, only date as in:
insert into mytable (funtionDate) values('3/6/2010')
No time; only date.
That's what I mean.
Sorry again, about the confusion.
Now it makes sense!
You were talking about the format you use to insert data (e.g. '3/6/2010')!
If you select the data from your table it will be displayed as '2010-03-06 00:00:00.000' because SQL Server performs an implicit conversion from character to datetime.
Regarding the format you use to insert data: I strongly recommend to change it to '2010306', which is YYYYMMDD). The way you do it may cause strange effects if someone will chage the setting of DATEFORMAT (e.g. by changing the language setting):
Try the following on your test system:
SET DATEFORMAT mdy
INSERT INTO myTable (functionDate) SELECT '3/6/2011'
SET DATEFORMAT dmy
INSERT INTO myTable (functionDate) SELECT '3/7/2011'
SELECT * FROM myTable WHERE functionDate >'20110101'
Instead of two entries for March (6th and 7th) you'll end up with one row for March 7th and one for June 3rd...
The changed setting for DATEFORMAT to dmy will cause an error if you try to insert date values with a day-of-month value >12 (e.g. '3/13/2011').
March 8, 2010 at 7:48 am
Lutz,
I can't thank you and the everyone else who assisted with this issue enough.
I can't thank you guys enough.
I just want to point out that I don't have the ability to insert, update, or delete records.
They just gave me readOnly access to the db.
But your advise, code, etc have been extremely helpful.
March 9, 2010 at 7:44 am
Greetings again,
I know you have given detailed, solid explanations of different ways of using the functionDate and GetDate() and I am truly grateful.
However, please forgive me for revisiting this issue again because it is still not solved.
The users sent me a spreadsheet today, showing what data they want to query to show.
For instance, the spreadsheet has shows data, including functionDate, showing today's date, as well as future date.
However, the query does not produce data that includes today's date.
Please take a look a the *actual* query and see where my mistake is coming from.
For instance, there is a table called defendantEventPros. This table stores among other fieldnames, the functionDate fieldname and if you run just a query to show all values for functionDate, you get dates from march 5, 2010 all the way till 2020.
However, the query below only shows dates from march 16 till march 19.
Am I doing the joins incorrectly?
Please take a look. Please notice that the query now is supposed to return data where functionDate is equal to, or greater than today's date.
SELECT GJ1_APD.IDNumber, GJ1_APD.OffLName, GJ1_APD.OffFName, GJ1_APD.DefLName, CONVERT(VARCHAR(10), DEP.functionDate, 101) AS CourtDate,
DEP.EventTime AS CourtTime, JD.Description AS JudgeName, CD.Description AS CourtNum, GJ1_APD.Charge, GJ1_APD.CaseNum,
DEP.EventComment AS Memo, GJ1_APD.DANumber, ED.Description AS Hearing, [PAD].Description AS DAName, CONVERT(VARCHAR(10),
GJ1_APD.functionDate, 101) AS SubpDate, GJ1_APD.Description AS Event
FROM dbo.vwGJ1_APD GJ1_APD
INNER JOIN dbo.DefendantEventPros DEP ON GJ1_APD.DANumber = DEP.VBKey
INNER JOIN dbo.EventDescription ED ON DEP.EventID = ED.EventID
LEFT OUTER JOIN dbo.CourtDescription CD ON DEP.EventCourtID = CD.CourtID
LEFT OUTER JOIN dbo.JudgeDescription JD ON DEP.Judge = JD.JudgeID
LEFT OUTER JOIN dbo.ProsAttyDescription [PAD] ON DEP.ProsAtty = [PAD].ProsAttyID
WHERE (DEP.functionDate >= dateadd(dd,datediff(dd,0,getdate()),0) AND (DEP.UserDelete = 0) AND (DEP.EventID = 654)
Lots of thanks.
March 9, 2010 at 7:55 am
It looks like you limit the data with one of the INNER JOINS from below.
Please reduce your query to those tables and check whether you'll get any values for today and/or tomorrow.
All other tables are used in a left join based on DEP so those should be fine.
FROM dbo.vwGJ1_APD GJ1_APD
INNER JOIN dbo.DefendantEventPros DEP ON GJ1_APD.DANumber = DEP.VBKey
INNER JOIN dbo.EventDescription ED ON DEP.EventID = ED.EventID
March 9, 2010 at 8:19 am
Thanks Lutz, you are the greatest.
Are you saying to remove those 2 tables for now or just test with those 2 tables only for now?
Ok, I just ran it with those 2 tables and still get dates starting March 16th through march 23rd.
SELECT GJ1_APD.IDNumber, GJ1_APD.OffLName, GJ1_APD.OffFName, GJ1_APD.DefLName, CONVERT(VARCHAR(10), DEP.functionDate, 101) AS CourtDate,
DEP.EventTime AS CourtTime, GJ1_APD.Charge, GJ1_APD.CaseNum,
DEP.EventComment AS Memo, GJ1_APD.DANumber, ED.Description AS Hearing, CONVERT(VARCHAR(10),
GJ1_APD.functionDate, 101) AS SubpDate, GJ1_APD.Description AS Event
FROM dbo.vwGJ1_APD GJ1_APD
INNER JOIN dbo.DefendantEventPros DEP ON GJ1_APD.DANumber = DEP.VBKey
INNER JOIN dbo.EventDescription ED ON DEP.EventID = ED.EventID
WHERE (DEP.functionDate >= CONVERT(varchar(8), GETDATE(), 112)) AND (DEP.UserDelete = 0) AND (DEP.EventID = 654)
March 9, 2010 at 9:55 am
So it looks like your data don't include any rows for (DEP.UserDelete = 0) AND (DEP.EventID = 654) that are between today and March 16th.
Try
SELECT * FROM DefendantEventPros
WHERE (functionDate >= CONVERT(varchar(8), GETDATE(), 112)) AND (UserDelete = 0) AND (EventID = 654)
I'm not really sure what you actually want to do...
Maybe you should provide the table def (related columns only) with some fake sample data but real values for functionDate, UserDelete and EventID so we have something to compare against.
March 9, 2010 at 10:06 am
Now, that shows the correct data.
It shows data from today till 2020, exactly what they want but not sure what/how those joins are affecting it.
I appreciate your patience and kindness.
March 9, 2010 at 10:09 am
For S & G's, change this:
INNER JOIN dbo.EventDescription ED ON DEP.EventID = ED.EventID
to this:
LEFT OUTER JOIN dbo.EventDescription ED ON DEP.EventID = ED.EventID
What do you get then?
March 9, 2010 at 10:09 am
Next step:
Include one more table in your select:
SELECT DEP.*
FROM dbo.vwGJ1_APD GJ1_APD
INNER JOIN dbo.DefendantEventPros DEP ON GJ1_APD.DANumber = DEP.VBKey
WHERE (DEP.functionDate >= CONVERT(varchar(8), GETDATE(), 112)) AND (DEP.UserDelete = 0) AND (DEP.EventID = 654)
Viewing 15 posts - 16 through 30 (of 47 total)
You must be logged in to reply to this topic. Login to reply