August 19, 2010 at 5:19 am
Dear all,
I'm very new at T-SQL and need your help for the following :
*
where month(DELIVERY_date)=8 and day(DELIVERY_date)=18
*
returns the data as I want, however
*
where DELIVERY_date=( GETDATE()-1)
*
DOES NOT return any data...I'd appreciate your help very much. Thanks in advance.
August 19, 2010 at 5:44 am
""""""GetDate also returns the time, would that be the cause of your problem?"""""
....No I don't think so ...The first line is also returns time..In fact I,also, need time data...
August 19, 2010 at 5:46 am
you'll get used to handling dates; this is one of the coolest things to use once you get your head wrapped around it.
the issue you are seeing is that getdate() returns the datetime down to the millisecond:
--results: 2010-08-19 07:36:26.617
SELECT getdate()
chances are nothing in your database matches to the millisecond, right?
what you want to do is remove the time portion of the getdate results, by using DATEADD functions ...you can use the CONVERT function, but best practice is to not jump back and forth between data types, and keep everything as datetime.
so to strip off the "time" portion, we do this:
--Midnight for the Current Day
select DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
--or more understandibly:
--Midnight for the Current Day
select DATEADD(dd, DATEDIFF(dd,'1900-01-01 00:00:00.000',getdate()), '1900-01-01 00:00:00.000')
dd = days...so what we are doing is using a function to find the difference in days between the beginning of time and today, and then adding the number of days till today to zero(the default starting date) taking the SQL start date for it's beginning of time.
by adding it to zero(SQL starting date '01/01/1900') with DATEADD(which returns a datetime), you get the starting date time plus the difference as the number of days. Mentally, i sometimes think of this method as "truncating" all the time portion, and then selecting the begining of the period.
the same concept works when you add months, years, or quarters...and even hours minutes seconds.....you get the # of [unit you selected] since the beginning of SQL Time, and by adding that #periods to zero(SQL start date), you get the beginning of that period.
in your specific case, where you want the day before, i would suggest doing it like this:
--substitute zero for the beginning of time date to make it shorter
select DATEADD(dd, DATEDIFF(dd,'1900-01-01 00:00:00.000',getdate() -1), '1900-01-01 00:00:00.000')
Lowell
August 19, 2010 at 5:48 am
also look at the data in delivery date: if it contains a time portion, you'll want to strip that time off as well to do your equality comparison.
--substitute zero for the beginning of time date to make it shorter
select DATEADD(dd, DATEDIFF(dd,'1900-01-01 00:00:00.000',DELIVERY_DATE), '1900-01-01 00:00:00.000')
FROM YOURTABLE
Lowell
August 19, 2010 at 5:50 am
where DELIVERY_date=( GETDATE()-1)
*
DOES NOT return any data...I'd appreciate your help very much. Thanks in advance.[/quote]
because your delivery_date is not the datetime type. Can you confirm this?
----------
Ashish
August 19, 2010 at 5:53 am
August 19, 2010 at 6:06 am
DEAR LOWELL,
Thank you very much for your detailed and educational reply 🙂
Although I do not want to stripp off the time portion unfortunately it did not work :ermm:
LEt me tell you beriefly what I'm doing :
I'm taking 6 columns from a table and 1 column from View and INNER JOIN them than with date filter I'm taking the report...
August 19, 2010 at 6:12 am
try using greater than instead of equal in your query:that way you get everything delivered since yesterday
where DELIVERY_date >= select DATEADD(dd, DATEDIFF(dd,0,getdate() -1), 0)
Lowell
August 19, 2010 at 6:12 am
Dear Ashish,
It is datetime....
UPPSS.....I guess I can not paste here a small size off print screen image :blink:
August 19, 2010 at 6:15 am
IT WORKED!!!!!! God Bless You Lowell...
August 20, 2010 at 12:22 pm
Hi
1)You can check DELIVERY_date column in the table it may be in datetime or Smalldatetime
2)If it is datetime/Smalldatetime and storing the in same datetime/Smalldatetime formate it will be stored as 2010-08-20 10:51:05.807 /2010-08-20 10:51:00
then you cant use DELIVERY_date=( GETDATE()-1) because it will check only when you query at 2010-08-21 10:51:05.807/2010-08-20 10:51:00 even millisecond
will be taken in to consideration while you run so you need to formate both DELIVERY_date and (GETDATE()-1)
as Convert(varchar,DELIVERY_date,103)=convert(varchar,GETDATE()-1,103) so it means that it will formate to that condition and then execute
Thanks
Parthi
Thanks
Parthi
August 20, 2010 at 12:48 pm
Parthi i mentioned before that while you can use CAST and CONVERT on the columns, it's best practice to stay within the same datatype by using DATE ADD/DATEDIFF functions;
when you use cast and convert, you end up using sql to do implicit conversions from varchar back to datetime...so you convert date time to varchar explicitly, then implicitly convert it back to the datetime, but in a time-truncated format as desired.
also, you'll see a massive performance hit due to the conversion, because you cannot use an index any more if it existed on the datetime column...functions on the column force a table scan.
Lowell
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply