• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7268
  • Last Modified:

manipulate dates in Sybase

Afternoon all I am working with a Sybase query that I need to do two things in:

1) I need to get the current date in the format of mm/dd/yyyy I have looked at convert and getDate() and I am not getting the exact format needed.

2) Once I get the current date I need to subtract a single day and end up with yesterdays date in the same format.

Any help you could offer would be great.

Thanks,
-Stephen
0
sunadmn
Asked:
sunadmn
  • 8
  • 6
1 Solution
 
grant300Commented:
To get the date in the string format you want, you have to use style 101, e.g.

select convert(varchar,getdate(),101)
returns "08/01/2007"

To get yesterdays date, you can use the DATEADD function to add -1 Day.  That looks like

select convert(varchar,getdate(),101) TodaysDate,
          convert(varchar,dateadd(day,-1,getdate()),101) Yesterday

Regards,
Bill
0
 
sunadmnAuthor Commented:
Ok great would this work then you think?

declare @today char(12),@time char(9),@yesterday char(12)
select @today=convert(char(12), getDate(), 101)
select @time=convert(char(9), getDate(), 108)
select @yesterday=convert(varchar,getdate(),101),convert(varchar,dateadd(day,-1,getdate()),101)


select @today,@time,@yesterday
0
 
sunadmnAuthor Commented:
Actually I just checked the DB and the table is actually getting the dates with a single digit month inserted like 7/31/2007 so I would need the format as such.


Thanks,
-Stephen
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
grant300Commented:
Strange, my test case returns the dates with leading zeros on both the month and the day.

select convert(varchar,getdate(),101),
convert(varchar,DATEADD(day,-1,getdate()),101)

returns....
      
08/01/2007      07/31/2007

Bill
0
 
sunadmnAuthor Commented:
I think it is the way the dates are being inserted into the DB in a non standard format here is an example:

set rowcount 1000

select INPUTDATE from FXDH

3/4/2005 12:00:00 AM  
1/18/2002 12:00:00 AM  
5/24/2002 12:00:00 AM  

This is a pain for me is there a trunc function in Sybase like Oracle that might work?

Thanks,
-Stephen
0
 
sunadmnAuthor Commented:
I think if I do the set dateformat dmy it will work for me
0
 
sunadmnAuthor Commented:
Ok so if I do this I am getting closer I just need to drop the timestamp off and I will be golden I think.

set dateformat mdy
select getDate()
,DATEADD(day,-1,getdate())

 column1              column2              
 -------------------  --------------------
 8/1/2007 4:08:13 PM  7/31/2007 4:08:13 PM

Any ideas there?

Thanks,
-Stephen
0
 
grant300Commented:
Is the INPUTDATE column in the FXDH table a DATETIME datatype, or a string?

If it is a string, the easiest thing to do is convert it to a datetime datatype with another CONVERT function before you do the DATEADD.

(Ignore this if the column is not a char or varchar)
As a rule, dates and datetimes should never be stored as strings in the database.  That is equivalent to storing numeric values as strings.  Would anyone of us database types want to do business with a bank that stored our account balances as strings instead of numbers?  I know I wouldn't.  Dates and strings lose most of the utility, screw up the sorting order, comparisons, and date math.  Performance is hit hard because every time you touch a date field, you have to wrap it with a function to convert it to and internal datatype; an expensive operation, and you can not us and index on the column effectively either.

The other thing that might be happening is that the leading zeros are disappearing as an artifact of whatever query tool you are using.

Bill
0
 
grant300Commented:
Yes, put the CONVERT functions back around the getdate() and dateadd functions.

Bill
0
 
sunadmnAuthor Commented:
Hmm very interesting Bill, and no this is setup as DATETIME. The query tool I am using is pretty good, but I suppose it could be the case.
0
 
sunadmnAuthor Commented:
Ok so I tried again with the CONVERT there and I still get back the mm/dd/yyyy even though I preceed that with the set dateformat..Hmmmm... I guess there is no real good way I can see to get the dates correct let me try another client and see if I can connect and get the same results.
0
 
grant300Commented:
O.K., I now no longer know what it is you want.  In your original question you state:

"1) I need to get the current date in the format of mm/dd/yyyy I have looked at convert and getDate() and I am not getting the exact format needed."

Now you are getting mm/dd/yyyy back.  What is the problem?????

0
 
sunadmnAuthor Commented:
Bill,


I apologize for the misleading statement there I made an assumption on my end that would be the format the dates would be in in the DB, but once I ran a query to validate the datatime in the DB I found that it was not in the format of mm/dd/yyyy rather m/dd/yyyy. I spoke to one of my DBA's here and this is what he said.

1/18/2002 12:00:00 AM    you are seeing month m as 1 because it  January an it can not be  2 digits.

So  I am going to assume that this should infact work for a query would you agree?

declare @today char(12),@time char(9)
select @today=convert(char(12), getDate(), 101)
select @time=convert(char(9), getDate(), 108)

SELECT MAX(INPUTTIME) LATEST, count(*) Number, DEALSRCE  FROM FXDH WHERE INPUTDATE=@today
AND DEALSRCE IN('EBS','EFOREX','FXALL','CALYPSO_OTC','D','WCPLUS','IFX','IBIS','FXOOB','REUTERS')
AND INPUTTIME <= @time  GROUP BY DEALSRCE ORDER BY Number Desc


Thanks again for your time here.
-Stephen
0
 
grant300Commented:
Thanks for giving us the entire scenario; it will make things easier.

In an earlier message, you state that INPUTDATE is a datetime Datatype as declared in table but now I see that you have an INPUTTIME column as well.  Since the INPUTDATE is of type datetime, it contains all the information you need and the INPUTTIME column would normally be irrelevant.

Please post the CREATE TABLE statement for the FXDH table.  Maybe a few rows of data if you can.

You have another problem as well.  If INPUTDATE is a datetime datatype, you will not be able to compare it to the shortened string in the WHERE clause unless the INPUTDATE was entered with just the date and not time specifier.  Just be cause you shortened @today to just be the date does not mean it will convert and compare with the INPUTDATE datetime datatype field.  All DATETIME datatypes store the date with the full time precision down to the Sybase clock tick (1/300th of a second) so exact comparisons are really exact.

If you enter a date only into a datetime field, it will have a time of 12:00:00AM and you can compare it with @today.  For performance reasons, I would cast @today as a datetime so that no conversion needs to be done on the fly.

declare @today datetime
set @today = convert(datetime,convert(varchar,getdate(),101))

This makes @today a datetime with the time set to 12:00:00AM and gives you like datatypes in the sarg.

How are you putting the time value in INPUTTIME and what is it's datatype?

Regards,
Bill
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now