getDate Format in UltraDev Code

[grumpy777]

I have code that reads below:

rsAds.Source = "SELECT AD_ID, AD_EXPIRED - getDate() AS DAYS, FROM ADS,

DAYS is referred to later in the script for display, my problem is the script is using statement to display data from a DB using " smalldatetime " in the AD_EXPIRED column.
I can't get around it displaying the date as - 1/31/1900 2:27:00 PM... what I want is in the format 3/19/2003.

I've tried going through the forum and none of the fixes seem to work...

Thanks...

 

[SQLSister]

It would help if you gave us a hint of what data you have and what your expected results are. If the current date is 3/19/2003 and the date expired is 2/12/2003 what would you expect to see as the value of days?

BY the way what is UltraDev? This is a Microsoft SQL Server forum.

 

[grumpy777]

This is data on my SQL Server and the data in the AD_EXPIRED column read 4/18/2003 2:27:00 PM.
What's being displayed is 1/30/1900 10:21:13 PM , and what I want to display is 4/18/2003 the expired date without the time.

 

[tlbroadbent]

The following will return an integer value for the number of days.

rsAds.Source = "SELECT AD_ID, convert(int, AD_EXPIRED) - convert(int, getDate()) AS DAYS FROM ADS"

Alternate:
rsAds.Source = "SELECT AD_ID, DateDiff(d, Ad_Expired, getdate()) As Days From Ads"

To see the Ad_Expired column in the format mm/dd/yyyy use the following SQL query.

Select Ad_ID, Convert(char(10),Ad_Expired,101) From Ads

Click the following link for more information about dates in SQL Server.

http://tlbroadbent.home.attbi.com/sql/ss_datetime.htm

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[grumpy777]

THANK YOU....

rsAds.Source = "SELECT AD_ID, convert(int, AD_EXPIRED) - convert(int, getDate()) AS DAYS FROM ADS"

This did it for me, I was going blind reading... I owe you big...

THANKS AGAIN...