?
Solved

HELP!!!! Date format

Posted on 2003-03-18
19
Medium Priority
?
278 Views
Last Modified: 2011-09-20
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
0
Comment
Question by:Debsje
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
  • +3
19 Comments
 

Expert Comment

by:BigBruce
ID: 8157766
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
 

Expert Comment

by:Breedj
ID: 8157791
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
 

Expert Comment

by:Breedj
ID: 8157811
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Expert Comment

by:BigBruce
ID: 8157853
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
 

Expert Comment

by:Breedj
ID: 8157917
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
 

Expert Comment

by:BigBruce
ID: 8157931
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
 

Expert Comment

by:BigBruce
ID: 8157983
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
 

Expert Comment

by:Breedj
ID: 8158026
Sorry BigBruce you are absolutely right. I was confused because of the typo I made.
0
 

Expert Comment

by:BigBruce
ID: 8158033
Not a problem.
0
 

Author Comment

by:Debsje
ID: 8158179
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
 

Expert Comment

by:BigBruce
ID: 8158220
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
 

Author Comment

by:Debsje
ID: 8158456
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
 
LVL 9

Expert Comment

by:xenon_je
ID: 8158590
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
 

Author Comment

by:Debsje
ID: 8158913
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
 
LVL 9

Accepted Solution

by:
xenon_je earned 300 total points
ID: 8159818
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
 

Author Comment

by:Debsje
ID: 8172189
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
 

Expert Comment

by:CleanupPing
ID: 9275777
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
 
LVL 12

Expert Comment

by:monosodiumg
ID: 11183412
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

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question