Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Jul 31, 2000

Converting Text to Date for Aging Amounts

Question:
The table I am using stores date values as strings due to conversion data. The format is 2000-07-01 for July 1, 2000. I need to be able to age amounts for an accounting report at any time, such as 0-30, 30-60, etc. Normally, I would just use a statement such as DATE(current date) - DATE(table_date) between 30 and 60, but it won't work because the "table-date" is not a DATE field. I've been hard-coding the dates and using the between statement, but that's not good for other users.

Can I change the string '2000-07-01' to a DATE field?

Answer:
Well, your column data type is varchar so you'll have to do the calculations in your SELECT statement. You can use the CAST function to convert the string to a date. Let's assume you have a table called TestTable with a datestring column and these values:

2000-06-01
2000-06-02
2000-06-15
You can use this query to find the aged records by comparing the datestring column to today's date:
SELECT datestring
FROM TestTable
WHERE GETDATE() - CAST(datestring AS datetime)
BETWEEN 30 AND 60
DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date