Date based query when date is nvarchar

MichaelTJ · January 12th, 2004, 07:09 PM

Hi all,
I have a table that contains posts from a forum. I also have a query that shows different posts depending on what group is selected. The creadtedDate field in the table is nvarchar (I know, I know... Just make it a date field...). Well, I can't make it a date field at this time due to the deadline I have and the field is WERY integrated in the code.

OK.. Over to what I need now (badly)
I need to make a query to this table that shows all posts older than a month. And that the whole query gets sorted by date desc.

Please help me here and I promise to change the field to a date field as soon as time alows it!

****************** DATA *****************************
Table: ryPosts
Field: CreatedDate
CreatedDate stored as: yymmdd hh:nn (031215 17:06)
************************************************** ***

If ANY more info is needed just let me know and you'll have it in 5min flat...

------------------------
All help is Good help!
Regards
Michael

Jeff Mason · January 12th, 2004, 08:25 PM

You can CAST the character string to a datetime, then select and sort on it; the DateDiff function will take character string that looks like a date as input (in SQL Server, anyway) so no CAST is necessary:

Code:

SELECT CAST(CreatedDate as datetime) as DateCreated, ...
WHERE DateDiff(m,CreatedDate,Current_timestamp)>=1
ORDER by DateCreated DESC;

BTW, your specification "...all posts older than a month..." can be subject to differing interpretations; do you mean 'in last month or before', or 'more than 30 or 31 days old', or what? The DATEDIFF function counts boundary crossings, so a post on Christmas last year would return a 1 in the expression above.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

MichaelTJ · January 12th, 2004, 08:35 PM

Hi Jeff,
Thanks for the quick reply!
I need the output to be all posts older than 30 days.

------------------------
All help is Good help!
Regards
Michael

Jeff Mason · January 12th, 2004, 09:43 PM

DATEDIFF(d,CreatedDate,Current_timestamp)>30 should work...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

MichaelTJ · January 12th, 2004, 09:57 PM

Thanks again Jeff,
I found it just as I refreshed this page.
I used this:
CreatedDate < (GETDATE() - 30)
It seems to be working ok, but if my luck is as usual I have to change to DATEDIFF.. he he he

Thanks again for your quick reply.. I might just make that deadline after all. *Smiles*

------------------------
All help is Good help!
Regards
Michael