Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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
Reply With Quote
  #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
Reply With Quote
  #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
Reply With Quote
  #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
Reply With Quote
  #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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 05:20 AM.


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