manipulate dates in Sybase

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

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

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

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

I think if I do the set dateformat dmy it will work for me

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

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

Yes, put the CONVERT functions back around the getdate() and dateadd functions.

Bill

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.

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.

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?????

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

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