Re: date field and SQL

[Thomas Bean]

Chris,



Hope this helps,

Thomas


Newsgroups: gmane.comp.crm.arsystem.general
To: <arslist@ARSLIST.ORG>
Sent: Thursday, April 10, 2008 4:43 PM
Subject: date field and SQL


Hey everyone,


I added a date field to a form and when we are trying to get the value
out in SQL we're not getting what we expect.  If I just do a select of
the field, I get a 7 digit number (2454436).  If I try and run the same
conversion against it that is run against the date/time fields:



convert (varchar(10),dateadd(second, Invoicemonth, '1 January 1970'),
101) 'Invoice Month 1'



we get 1/28/1970.  I'm no SQL expert- how can I get the value of that
field in a MM/DD/YYYY format?



Thanks!

Chris


_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"

_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"

For MS-SQL, you can use the attached function. It will convert the date field's integer value into a SQL datetime value. Keep in mind that the minimum value for a datetime field in MS-SQL is January 1, 1753 (Remedy date fields go back to January 1, 4713 B.C.). So, I wrote the function to simply return 01/01/1753 for any Remedy dates that fall earlier than the MS-SQL minimum. ----- Original Message ----- From: "Moore, Christopher Allen" <[EMAIL PROTECTED]>

DateOnlyInt_to_datetime.sql
Description: Binary data