Need help with a date manipulation query

[rogers42]

Hi Folks,

I am working on a (hopefully) one time assignment to pull some data from an sql server. The data needs to be pulled for a particular day.

While I am familiar with Oracle, I do not know the SQL syntax (on Sql server) to formulate the correct query.

Sample Date data looks as follows
"1/1/2007 12:24:18 AM"

Can somebody please convert the following Oracle query into something that an SQL server might understand ?

select *
from table_A
where trunc(CREATE_DATE) >= to_date('11/JUN/2008', 'DD/MON/YYYY')
and truncate(CREATE_DATE) < to_date('11/JUN/2008', 'DD/MON/YYYY')
/

Thanks in advance

rogers42

 

[gmmastros]

Select *
From   Table_A
Where  CREATE_DATE >= '20080611'
       And CREATE_DATE < '20060612'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[rogers42]

Hi,

I have tried the suggested query, and it keeps timing out.

Is it safe to assume that "DD/MM/YYYY" format will automatically be taken care of ? How about the time component (i.e. 12:24:18 AM)? Would the time component auto truncated ?

Thanks in advance

rogers42

 

[gmmastros]

>> Is it safe to assume that "DD/MM/YYYY" format will automatically be taken care of ?

Not really. For example, you could have a date that looks like 2/3/2008. Is this Feb 3, or March 2? It depends on the language setting for the login used to connect to the database. There are only 2 safe (non-ambiguous date formats) for sql server. YYYYMMDD and YYYY-MM-DDTHH:MM:SS

What is the data type for the CREATE_DATE column? If it's DateTime or SmallDateTime, then the code I posted should work well. Notice the way the query is written. >= SomeDate And < SomeDate + 1

If your CREATE_DATE is not a datetime (or SmallDateTime) data type, then you will need to write your query a different way. Also, you may be getting time outs because your column is not indexed.

I can help you with this some more, but I'll need to know some additional info.

Run...

Select Data_Type From Information_Schema.Columns Where Table_Name = 'Table_A' And Column_Name = 'CREATE_DATE'


And this...

sp_Helpindex 'Table_A'



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[rogers42]

Appreciate the help.

The date format is MM/DD/YYYY HH:MM:SS AM

The results of the two queries are as follows

First Query:
datetime

Second Query:
idxCMREQLOG_ComplTime nonclustered located on PRIMARYCOMPLTIME, STATUS, EXTENDEDSTATUS, SETTOP

idxCMREQLOG2 clustered, ignore duplicate keys, unique located on PRIMARY REQTIME, ID

idxWHCM_CMREQLOG_WASSET_SETTOP_REQTIME nonclustered located on PRIMARY WASSET, SETTOP, REQTIME
WHCM_CMREQLOG_PK_WHID nonclustered, unique, primary key located on PRIMARY WHID

Let me know if this helps.

Thanks

rogers42

 

[gmmastros]

The date format is MM/DD/YYYY HH:MM:SS AM

The results of the two queries are as follows

First Query:
datetime

First, realize that this is NOT the format of the column. This is the format for SHOWING the data in the column from whatever application you are using (Query Analyzer, Enterprise Manager, SQL Server Management Studio, etc....)

Internally, the dates are stored as 2 integers. It's a bit confusing how this is stored, but from a higher level perspective, it doesn't really matter.

If you are really curious....

http://dotnetsamplechapters.blogspot.com/2007/09/how-are-dates-stored-internally-in-sql.html

Now....

Since you are getting time out errors, I suspect that you have a lot of rows in your table. As such, you should know that creating indexes can take some time to create. While they are creating, they can negatively impact performance for other users in your system. However, I suspect that an index is exactly what you need for this query. I recommend you create an index on your CREATE_DATE column.

Something like this...

Create NonClustered Index idxCMREQLOG2_CreateDate On [!]YourTableName[/!](CREATE_DATE)

If you have a DBA where you work, you may want to suggest this index to him/her. Like I said earlier, creating indexes can take some time (especially with large table). Additionally, they require disk storage space, so the size of your database may grow.

Also.... I noticed a problem with the original query I posted. The end date is wrong. It should really be...

Select *
From   Table_A
Where  CREATE_DATE >= '20080611'
       And CREATE_DATE < '200[!]8[/!]0612'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[rogers42]

Hi,

Yup, I had caught the fat fingering the first time around. Yes, the query worked this time. I guess, it has something to do with the load on the system.

Yes, I also agree that indexing is required. However, given the inter depratmental politics etc, this is a luxary that I can't afford.

My next challange is to retrieve data in chunks (i.e. get the first 100 rows, copy & paste the data, then the next 100, ....). Is this something that is easily doable ?

Thanks

rogers42