January 4, 2007 at 3:48 pm
Hi there,
I have spent days working on this, and I'm just not getting anywhere, so I would appreciate some help, or some hints..
I have a column that is numeric 9, that stores dates in the format YYYYMMDD, and I would like to perform date arithmetic on this column (eg, getdate() - 30).
Any ideas?
thanks.
January 4, 2007 at 4:12 pm
This will do it:
PRINT CAST(CAST(20070105 as VARCHAR(8)) AS SMALLDATETIME)
Be wary of the overhead this will add in a query.
SQL guy and Houston Magician
January 4, 2007 at 5:52 pm
thanks - I'll give it a go!
January 4, 2007 at 10:57 pm
Don't use "getdate() - 30". Use the DateAdd() function.
January 5, 2007 at 3:09 am
You should also consider to change the datatype to a datetime, then all 'number to date problems' goes away.
If you have data that is a date, it should be treated as a date also.
/Kenneth
January 5, 2007 at 6:12 am
If you absolutely MUST have your date stored in YYYYMMDD format, then why not create another column in the table that simultaneously records the same date in the more traditional smalldatetime datatype?
Then you have the best of both worlds!
January 5, 2007 at 9:01 am
...and taking that a small step further, make it a persisted computed column, so it's done once and automatically with application involvement.
Mark
January 5, 2007 at 11:16 am
That's what I would do. I would store the value as a datetime or smalldatetime and then use a persisted computed column for the integer value. Like so:
MyDate smalldatetime NOT NULL
iMyDate AS (CONVERT(int,CONVERT(varchar, MyDate,(112)),0)) PERSISTED
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply