Get Date - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Get Date

Author

Topic

ladiaocb
Yak Posting Veteran

57 Posts

Posted - 2004-11-04 : 14:15:49

I am having a problem trying to run the following query

Select * from table1
where sent_date = getdate()-1

This is not returning any rows. I know for sure there is data that dates yesterday's date.

An example of the sent_date row is: 2004-10-27 00:00:00.000

When I do a GETDATE() it returns: 2004-11-04 11:17:48.630

I know i have to convert the getdate() to match the sent_date. Please help!!

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-11-04 : 14:23:26

http://www.sqlteam.com/item.asp?ItemID=240

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-04 : 14:29:35

I like the "Arnold Fribble" technique to get just the date portion of a datetime value:

select dateadd(d, datediff(d, 0,getdate()),0)

To get the day before, just use:

select dateadd(d, datediff(d, 0,getdate()),-1)

- Jeff

Kristen
Test

22859 Posts

Posted - 2004-11-04 : 14:35:18

Select * from table1
where sent_date >= DATEADD(Day, DATEDIFF(Day, 0, getdate()), 0)
AND sent_date < DATEADD(Day, DATEDIFF(Day, 0, getdate()), 1)

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-04 : 15:58:04

I like this way to remove the time portion of a datetime:

select dateadd(d,-1,cast(getdate() as int)) as yesterday
select dateadd(d,0,cast(getdate() as int)) as today
select dateadd(d,1,cast(getdate() as int)) as tomorrow

rockmoose

ladiaocb
Yak Posting Veteran

57 Posts

Posted - 2004-11-04 : 16:00:49

Thanks all. They all worked great!!!

Kristen
Test

22859 Posts

Posted - 2004-11-05 : 01:51:57

Spoilt for choice!

MooseThatRocks: I worry that

select dateadd(d,-1,cast(getdate() as int)) as yesterday

relies on a side effect and that Microsoft might change the storage format in the future. I suppose there is so much code relying on that that they won't be able to change the format ...

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-05 : 04:28:06

Yes you are right Kristen, it relies on the fact that each day is "mathematically" 1 long.
If they change the represention and you move the code to the new platform... uh ... breaks.

Found an interesting kbase article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;71441

It has a leapyear algorithm ( i know i wrote that one from scratch a few times

Also, does anyone have any comments on the technique of storing dates as an int,
as proposed by kbase article ?:
declare @birthday int
set @birthday = 20050224

That is like an intelligent key, with inherent logic.

rockmoose

Kristen
Test

22859 Posts

Posted - 2004-11-05 : 08:10:14

One of the databases (Oracle as it turns out) that we import data from has all the dates as an INT of second-since-01Jan1970 - some Unix diehards I suppose!

I'm for ever typing SELECT DATEADD(Second, SomeColumn, '01Jan1970') to see what the data is - so I would propose that not being able to see the date when you type SELECT * would be a PITA.

Doesn't Yukon have a DATE datatype?

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-05 : 08:56:29

Yes, seeing the date is a bonus that is not granted to everyone

I have something like fnGetDate(@int date_id) which formats a date key to humanreadable stuff.
You could do a similar udf to do that, or even if it is major PITA, consider computed columns or views.

With the tecnique in the article you do get to see the date though.

Yukon, I have read, have a DATE dt and a TIME dt.
Yes NTH.

rockmoose

Kristen
Test

22859 Posts

Posted - 2004-11-05 : 11:47:42

fnGetDate(@int date_id) eh? Yeah right ...


SELECT	*
FROM	OPENQUERY(MyOracleDB, 
'
SELECT	fnGetDate(CHANGE_DATE)
FROM	CUSTOMER
WHERE	CHANGE_DATE >= ' + CONVERT(VARCHAR(20), DATEDIFF(Second, '01Jan1970', '02Nov2004')) + '
ORDER BY CHANGE_DATE')

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-05 : 12:02:55

Hehehe,
SELECT fnGetDate(CHANGE_DATE) FROM OPENQUERY(...)

BTW,
Trying to set up linked server to Oracle without installing the Oracle Client.
We are using Oracle 10g Instant Client, which has a much smaller footprint.
Have you tried that software ? or managed to get it working ?

rockmoose

Kristen
Test

22859 Posts

Posted - 2004-11-05 : 12:42:52

Yeah, obviously, but my * was a pretence at having a complicate query at the far end and not having to mess about with local stuff. I suppose I have just got used to typing DATEADD(Second, SomeColumn, '01Jan1970'), and at my age its hard to set me on a new course ...

Given that I try to avoid Oracle like the plague I'm not much help on the Client front I'm afraid. I get the, errmmm ... "Client" to install a sutable errmmmmm ... "Client" and then spend a day messing around with the LinkedServer SProcs until I strike lucky and get connected!

Having said that the hourly "What's changed on the Mother Database" takes 10 minutes, or so, to run so maybe I should get a better client. I don't know, off hand, how much of that is the poxy Oracle View that works out the current stock levels - it seems to calculate loads of stuff about "what's reserved" and so on, and because we need stock levels for all product lines I suppose its reasonable that it does take a while - and how much of it is the couple of '000,000 rows that actually get transferred.

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-05 : 12:54:15

We load the whole oracle debacle into a staging area.
Monthly or daily basis.
For the largest files we pass a date param to only retrieve the lates rows,
if that is not possible we get the whole thing. ( max ~5.000.000 rows )
Then we do all the stuff inside sql server.

quote:
and then spend a day messing around with the LinkedServer SProcs until I strike lucky and get connected!

sounds familiar

Then we have the sybase, lotus notes, as400, db2 stuff as well.
Why can't they make drivers that are easy to configure, and .work. ?
Of course MS OLEDB Provider for OLAP SERVICES is not very nice either

And the error messages ... Laughable

Sybase complained that the returned string was only 13 char long, but it expected 25 doh!, nulls were ok though.
That was the ole db driver.
The odbc driver worked though.

rockmoose

Kristen
Test

22859 Posts

Posted - 2004-11-06 : 02:07:24

We only have about 5 million rows to pull from Oracle too. Trouble is they need the "other database" (Ours

) up to date every 10 minutes. So we are querying Oracle based on CHANGE_DATE. Every table has a CHANGE_DATE. Fantastic! "Are they reliable?" "Absolutely". Been running a month ... guess what? "Gee, some imported values seem to be incorrect on your database ..." <thud>

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-06 : 05:28:29

I would probably do a full refresh of the 5000000 records on a periodic basis.
Trust the CHANGE_DATE, Yeah right!
Then you could log any records that have been changed after the CHANGE_DATE date

IME quite often there are individuals or programs that don't honor the requirement to change a CHANGE_DATE when modifications are done to the table.

rockmoose

Kristen
Test

22859 Posts

Posted - 2004-11-06 : 16:53:47

The client is scared stiff of the application provider, and won't change any tables direct (which is a good thing, really). So I can blame the application provider without hesitation!

Kristen

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-11-07 : 09:30:18

quote:

I like this way to remove the time portion of a datetime:

select dateadd(d,-1,cast(getdate() as int)) as yesterday
select dateadd(d,0,cast(getdate() as int)) as today
select dateadd(d,1,cast(getdate() as int)) as tomorrow

I hope you don't use that, because it doesn't work!

SELECT CAST(CAST('20041107 10:00:00' AS datetime) AS int)
SELECT CAST(CAST('20041107 14:00:00' AS datetime) AS int)

return

38296
38297

respectively. Converting to an integer produces a number that changes at midday rather than midnight.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-07 : 17:18:21

Thanx for the correction Arnold,
I counted on an implicit cast being done that would floor the datetime for me.

The correct way would be:

select dateadd(d,-1,floor(cast(getdate() as float))) as yesterday
select dateadd(d,0,floor(cast(getdate() as float))) as today
select dateadd(d,1,floor(cast(getdate() as float))) as tomorrow

What's happening here ?

SELECT	CAST(CAST('20041107 10:00:00' AS datetime) AS float),
	CAST(CAST('20041107 14:00:00' AS datetime) AS float),
	CAST(CAST('20041107 10:00:00' AS datetime) AS int),
	CAST(CAST('20041107 14:00:00' AS datetime) AS int) [?!?!?],
	CAST(38296.416666666664 AS int),
	CAST(38296.583333333336 AS int)

                                                                          ?!?!?                               
------------------------------ ------------------------------ ----------- ----------- ----------- ----------- 
38296.416666666664             38296.583333333336             38296       38297       38296       38296

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources