0

I have a database column defined as int that contains a negative number representing a date in mmddyy format (without the leading zero)

so -10209 = 01/02/2009
-122508 = 12/25/2008

How can I convert the data to a datetime so I can compare it to another date in a where clause like :

..... where (converted data) > '2010-01-01'

thanks

2
Contributors
2
Replies
3
Views
7 Years
Discussion Span
Last Post by Brick
0

Here's my stab at it.

Select
RIGHT(CONVERT(varchar,abs(-11009)), 2),
SUBSTRING(CONVERT(varchar,abs(-11009)),LEN(CONVERT(varchar,abs(-11009)))-3,2),
SUBSTRING(CONVERT(varchar,abs(-11009)),LEN(CONVERT(varchar,abs(-11009)))-5,2),
CONVERT(DateTime,SUBSTRING(CONVERT(varchar,abs(-11009)),LEN(CONVERT(varchar,abs(-11009)))-5,2)
+ '/'
+ SUBSTRING(CONVERT(varchar,abs(-11009)),LEN(CONVERT(varchar,abs(-11009)))-3,2)
+ '/'
+ RIGHT(CONVERT(varchar,abs(-11009)), 2))

The first three lines after the select are just me pulling the pieces apart as a proof of concept, the lines starting at CONVERT(DateTime, ... are the relevant lines for this exercise.

Whose idea was it to store a date as a negative number anyway? And with only two places for the year! To think, the Y2K scare was only a decade ago.

Edited by apegram: n/a

0

Excellent, thank you.

Not my idea to store date like that, gonna have to fix that too

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.