HELP!!!! Date format

Okay, I've searched everywhere and read a lot of similar problems, tried out those answers but I always end up with an error message :-(  I really hope that there is someone here who can help me out!

Piece of code:
Set RS= Triton.GetTritonData("select T$ORNO, T$CUNO, T$DDAT from TTDSLS040101 where to_char(T$DDAT,
" & "'DD/MM/YY') = " & "'05/03/03'order by T$ORNO" )  

This works perfectly, the result are all the ordernumbers (T$ORNO) of that date.
The problem is that I have to fill in a variable that contains a certain date (in stead of '05/03/03').
I am now trying to get the results from today using GetDate() but I can't find the right answer.

Things I've tried but don't work (message : missing expression, invalid column name):

Set RS= Triton.GetTritonData("select T$ORNO, T$CUNO, T$DDAT from TTDSLS040101 where to_char(T$DDAT,
" & "'DD/MM/YY') = FormatDateTime(GetDate(),2)" )
Set RS= Triton.GetTritonData("select T$ORNO, T$CUNO, T$DDAT from TTDSLS040101 where to_char(T$DDAT,
" & "'DD/MM/YY') = CONVERT(datetime, Getdate(), 101) " )    
Set RS= Triton.GetTritonData("select T$ORNO, T$CUNO, T$DDAT from TTDSLS040101 where to_char(T$DDAT,'MM/DD/YY') = to_char(GetDate(),'MM/DD/YY')")  

So it must be like this:
Set RS= Triton.GetTritonData("select T$ORNO, T$CUNO, T$DDAT from TTDSLS040101 where to_char(T$DDAT, " & "'MM/DD/YY') = Date of today (but compared the right way (MM/DD/YY)

Is there anybody who knows the answer???
If you can help me, I would be very very happy!
Tx
DebsjeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BigBruceCommented:
The way I have always gotten around this one is by using the datediff function.  

So it would look something like this.

select
  T$ORNO,
  T$CUNO,
  T$DDAT
from
  TTDSLS040101
where
DateDiff(dd,T$DDAT,GetDate()) = 0

Now I have to admit I have never seen the to_char() function in MS SQL.  It seems to be a Oracle function.  If this is the case then try looking at this post:

http://www.experts-exchange.com/Databases/Oracle/Q_20257731.html

This is some information about Oracle and Dates.  

Hope this helps.
0
BreedjCommented:
Be aware, getDate() does not only return the date of today, but also the current time with a resulution of milliseconds.

So use if you want a resolution of a day.

select T$ORNO, T$CUNO, T$DDAT from TTDSLS040101
where DATEPART(d, getDate()) = DATEPART(dd, T$DDAT)
and DATEPART(m, getDate()) = DATEPART(mm, T$DDAT)
and DATEPART(m, getDate()) = DATEPART(yyyy, T$DDAT)
0
BreedjCommented:
Note:

The solution by BigBruce:

select
 T$ORNO,
 T$CUNO,
 T$DDAT
from
 TTDSLS040101
where
DateDiff(dd,T$DDAT,GetDate()) = 0

will not work for you, because it will return all the rows which have a difference of 24 hours with the current time. So it when you are running the query at 9:00AM you will also get rows inserted after 9:00AM the day before.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

BigBruceCommented:
Run these commands in Query analyzer.  

Select datediff(dd,'03/17/03',GetDate())
Select datediff(dd,'03/18/03',GetDate())
Select datediff(dd,'03/19/03',GetDate())

The first one give you 1 the second 0 the third -1 given that getdate returns 03/18/03.  

If you check the documentation DateDiff uses the first parameter to do the calculations.  If you dd or d then the day is used and that is the difference Returned.  Now this is in SQL 2000.  I believe it is the same in SQL 7.0 as well.  6.5 I could not tell you for sure.  

Also checking the Help files from MS under DateDiff will fully explain how the calculations are returned.

If you have MS SQL Books online installed in the normal directory on your computer then this URL should pull up the date diff help page.

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsqlref.chm::/ts_da-db_5vxi.htm
0
BreedjCommented:
You are right if you use char or varchar, but I thought that the variable was of type datetime. If it is then it will contain time as well. When translated it will be like below and that does not work.

Select datediff(dd,'03/17/03 00:00:00.000',GetDate())
Select datediff(dd,'03/17/03 00:00:00.000',GetDate())
Select datediff(dd,'03/19/03 00:00:00.000',GetDate())
0
BigBruceCommented:
Run these commands in Query analyzer.  

Select datediff(dd,'03/17/03',GetDate())
Select datediff(dd,'03/18/03',GetDate())
Select datediff(dd,'03/19/03',GetDate())

The first one give you 1 the second 0 the third -1 given that getdate returns 03/18/03.  

If you check the documentation DateDiff uses the first parameter to do the calculations.  If you dd or d then the day is used and that is the difference Returned.  Now this is in SQL 2000.  I believe it is the same in SQL 7.0 as well.  6.5 I could not tell you for sure.  

Also checking the Help files from MS under DateDiff will fully explain how the calculations are returned.

If you have MS SQL Books online installed in the normal directory on your computer then this URL should pull up the date diff help page.

mk:@MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsqlref.chm::/ts_da-db_5vxi.htm
0
BigBruceCommented:
Sorry about the dup post.  Hit refresh.

Try again.

Your second command has 03/17/03 and it should have 03/18/03.

Still comes up 1, 0, -1.  Now if you really want a test run this:

Declare @DateTest1 as datetime
Select @DateTest1 = convert(varchar,'03/17/03 11:59:00.000 PM')
Select datediff(dd,@DateTest1,GetDate())

Declare @DateTest2 as datetime
Select @DateTest2 = convert(varchar,'03/18/03 12:00:00.000 AM')
Select datediff(dd,@DateTest2,GetDate())


Declare @DateTest3 as datetime
Select @DateTest3 = convert(varchar,'03/19/03 12:00:00.000 pM')
Select datediff(dd,@DateTest3,GetDate())

Here we take a Datetime var and run it through Getdate.  No I changed the time and date in each example.  The first is 3/17/03 at 11:59 PM (This is the one that you say will show up on the same day).  It returns a 1.  The next on is on 03/18/03 at 12:00:00.000 AM.  This is about 2 hours ago my time.  It shows up as 0.  Then there is 03/19/03 at 12:00:00 PM or noon tomarrow.  This one will give you a -1.  Now I *really* know that this is the way that the datediff works.  If it didn't then I would have a VERY VERY upset customre with over 500 ASP pages that would need to be rewritten.  


Here is the DateDiff on MS's site.  
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_5vxi.asp

Now in the remarks section they say:
"The method of counting crossed boundaries such as minutes, seconds, and milliseconds makes the result given by DATEDIFF consistent across all data types. The result is a signed integer value equal to the number of datepart boundaries crossed between the first and second date. For example, the number of weeks between Sunday, January 4, and Sunday, January 11, is 1."

So it counts all the differnet parts and then gives you the answer.  It doesn't take 24 hours from now or do a +/-
720 min difference from now.  

Run the SQL I gave you check the results and also check you post.  The date is off.

0
BreedjCommented:
Sorry BigBruce you are absolutely right. I was confused because of the typo I made.
0
BigBruceCommented:
Not a problem.
0
DebsjeAuthor Commented:
Well, I must say I was surprised to find this much response. However, it seems that all of you know lots about this topic and I am just a beginner so some things are a bit difficult to understand.
I've tried the DateDiff but there still is an error message.
And indeed, it is oracle.

I just don't understand that it works fine when you fill in a date, but when you want to do it otherwise, it suddenly becomes so difficult!
I am now searching on it for 2 days and I don't like it anymore :-)

I'm not an expert on these things (hoping to become one though), I am still a student and I have to work on a project in a certain company during 11 weeks before I can graduate. So this is the project I must finish (hopefully working) before I can go working next year.

So I would appreciate it a lot if everybody who knows something about things I ask, would help me a bit :-)
Thanks!!!
0
BigBruceCommented:
Try this:

select T$ORNO, T$CUNO, T$DDAT from TTDSLS040101 where to_char(T$DDAT,'YYMMDD') = to_char(GetDate(),'YYMMDD')  

I don't have an oracle server so I can't do any testing, but also try

Select to_char(getdate(),'YYMMDD')
Select to_char(T$DDAT,'YYMMDD')

and match them up visually.  make sure they are coming out of the way you expect.

The last thing I can suggest is to see the GetDate() results.  and see if there is anything different with that.  And the very last thing I would do is put a question in the Oracle Area :) This is the Microsoft SQL Area.  

Good luck!  I'm happy to help if I can.
0
DebsjeAuthor Commented:
Well, I must say I was surprised to find this much response. However, it seems that all of you know lots about this topic and I am just a beginner so some things are a bit difficult to understand.
I've tried the DateDiff but there still is an error message.
And indeed, it is oracle.

I just don't understand that it works fine when you fill in a date, but when you want to do it otherwise, it suddenly becomes so difficult!
I am now searching on it for 2 days and I don't like it anymore :-)

I'm not an expert on these things (hoping to become one though), I am still a student and I have to work on a project in a certain company during 11 weeks before I can graduate. So this is the project I must finish (hopefully working) before I can go working next year.

So I would appreciate it a lot if everybody who knows something about things I ask, would help me a bit :-)
Thanks!!!
0
xenon_jeCommented:
Debsje there are in Oracle functions that affect the format of a date.I don't have Oracle at me, but I remember there is a function that converts a date to a string, in a certain format. Try to use it.
Getdate() returns the date and time, but you need only the date!! that's why you will use something like:

Set RS= Triton.GetTritonData("select T$ORNO, T$CUNO, T$DDAT from TTDSLS040101 where to_char(T$DDAT,
" & "'DD/MM/YY') = " & "datetochar(getdate(), format) order by T$ORNO" )  

As you can see datetochar is just a name I used , because I'm not sure there is a function with this name, but there is sure a function with this kind of functionality

gl

0
DebsjeAuthor Commented:
Well, thanks all of you, but especially xenon_je!!
I found a book with those things in it and there I read that it is also to_char for a date. I tried that before with GetDate() indeed but that is the thing that wasn't working! So I used sysdate, as was in the book, but that gave an error as well :-( So I thought, maybe in capital letters, and yes, it works!! Woehoe!!!

The problem now is, I must get the date out of a variable, filled up earlier in the program, and I haven't got any idea how to get that in the sql-statement.
When I fill in the name of the variable in stead of SYSDATE, it says 'invalid column name'.
Anybody who knows what to do?

Maybe t is better to explain a bit how things musr-t work, just so you van visualise things more :-)

The thing I have to make is an overview of the orders of the company.
There is a table with 5 columns, each column represents another day, the first column represents today, the second tomorrow, etc. But saturdays and sundays may not be seen so I can't just select the dates like today, today+1, today+2, today+3 and today+4 because then saturdays en subdays are included as well. (except when today is a monday)
So I found some code to make sure that the right day is in the right column. For each column I made another variable wherein I put the date of the column and it is that variable I should be able to use in the sql-statement.

If you could come up with an answer, please do!
THANKS!!!
0
xenon_jeCommented:
So if you try something like this, you receive an error?
:

Set RS= Triton.GetTritonData("select T$ORNO, T$CUNO, T$DDAT from TTDSLS040101 where to_char(T$DDAT,
" & "'DD/MM/YY') = " & YourVariable &" order by T$ORNO" )  

where YourVariable is the variable containing a string (a string with the date in it)

Can you please write the sample code with the variable you used that didn't worked???
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DebsjeAuthor Commented:
Well, it's ok, you don't have to break your heads anymore, I've already found it!! (thanks to my mentor here at the company)

SQL = "select T$ORNO, T$CUNO, T$DDAT from TTDSLS040101 where T$DDAT >= To_Date(" & "'" & FormatDateTime(date(),2) & "'" & ", 'MM/DD/YY" & "')" & " AND T$DDAT <= To_Date(" & "'" & FormatDateTime(DateAdd("d", AddDays, date()),2) & "'" & ", 'MM/DD/YY"  & "') ORDER BY T$ORNO"

The first column always contain the date of today, the next 4 are the next 4 working days.
So, if today is a Monday, it goes from today until today + 4 (AddDays = 4). If today is another day, it goes from today until today + 6 (AddDays = 6) so that saturdays and sundays are not concluded.

That is at least one big problem solved, now I have to make my code smaller because I am writing it to elaborate :-(

Wish me good luck!
xxx
0
CleanupPingCommented:
Debsje:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
monosodiumgCommented:
No comment has been added to this question in more than 268 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
   Accept: xenon_je http:#8158590

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

monosodiumg
EE Cleanup Volunteer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.