Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old January 12th, 2004, 07:09 PM
Authorized User
 
Join Date: Oct 2003
Location: , , Norway.
Posts: 45
Thanks: 0
Thanked 0 Times in 0 Posts
Default Date based query when date is nvarchar

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
__________________
------------------------
All help is Good help!
Regards
Michael
  #2 (permalink)  
Old January 12th, 2004, 08:25 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
  #3 (permalink)  
Old January 12th, 2004, 08:35 PM
Authorized User
 
Join Date: Oct 2003
Location: , , Norway.
Posts: 45
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #4 (permalink)  
Old January 12th, 2004, 09:43 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
  #5 (permalink)  
Old January 12th, 2004, 09:57 PM
Authorized User
 
Join Date: Oct 2003
Location: , , Norway.
Posts: 45
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
convert data from nvarchar to Date Time rahulgzb SQL Server 2000 4 September 7th, 2007 09:45 PM
how to show 2 different values based on date maria Crystal Reports 1 August 4th, 2004 08:21 AM
Query based on date calculation? skinny Access 5 September 30th, 2003 03:20 PM
Results based on Date hcweb Classic ASP Basics 1 August 12th, 2003 02:28 PM
Convert String Date to Date for a SQL Query tdaustin Classic ASP Basics 4 July 7th, 2003 06:01 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.