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

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.

Excellent, thank you.

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.