date stored as int

Question

Post reply

date stored as int

cathie corcoran

SSC Enthusiast

Points: 145

July 1, 2004 at 4:27 am

#161575

hi!

im fairly new to sql server and i have a problem with a date field.

i have a query that selects name and address of debtors and total amount due. i do not want to include the transaction date in the select statement as then the sum of the amount is not calculated and each row is returned individually.

the other problem is that the date is stored in the database as an integer.

what i need to do is use the transaction date in the where clause as i want users to be able to enter a parameter date and then get all transasctions where the transaction date is 90 or more days before the parameter date.

i tried using cast and convert in the where clause but think these can only be used in the select statement.

here is my query:

SELECT SE.ACCNT_CODE, SC.ACCNT_NAME, SD.ADDRESS_2, SD.ADDRESS_3, SD.ADDRESS_4, SD.ADDRESS_5, SUM(SE.AMOUNT*-1) AS Total_Amount_Outstanding FROM SSRFADD SD INNER JOIN SSRFACC SC ON SD.ADD_CODE = SC.ADD_CODE INNER JOIN SALFLDGSDE SE ON SC.ACCNT_CODE = SE.ACCNT_CODE

WHERE (SC.ACCNT_CODE BETWEEN 'D000000' AND 'D999999')

AND SC.SUN_DB = 'SDE'

AND SD.SUN_DB = 'SDE'

AND (SE.JRNAL_TYPE <> 'DR09' OR SE.JRNAL_TYPE <> 'DR13')

AND SE.ACCNT_CODE LIKE 'D%'

AND SE.ACCNT_CODE = SC.ACCNT_CODE

AND (SE.ALLOCATION in (' ') )

GROUP BY SE.ACCNT_CODE, SC.ACCNT_NAME, SD.ADDRESS_2, SD.ADDRESS_3, SD.ADDRESS_4, SD.ADDRESS_5

order by SE.ACCNT_CODE

would really appreciate any suggestions

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

You can use CAST and CONVERT in the WHERE clause however, depending on the format of the date as an integer it may not work. Can you post some examples of how the date is currently stored so we can see it?

Good Luck,

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Jeswanth Jaishanker SSCommitted Points: 1869 More actions · Answer 2

Hi

Even we are doing the same way (Storing date as int) but how we handle the same is as follows.

In SQL Server if you use cast or Convert there will be a diffrence of 1 or 2 days (Handle the same by hardcoding + 1 or - 1 ) I am not sure about the diffrence it gives. then another easy method is that in the UI level it se;f convert the date to Long (In VB) and call the Stored Procedure so inside the procedure you dont have to call the convert or Cast. Hope it will solve your problem

Jeswanth

--------------------------------

vadba SSChampion Points: 11133 More actions · Answer 3

-- If your integer date is of the form 'yyyymmdd', try this example:

DECLARE @d int

SET @d = 20040702

SELECT @d, Convert(datetime, Convert(varchar(8), @d))

-- If it's in some other format, such as 'mmddyyyy' or 'mmddyy',

-- then you will have to rearrange it a bit.

-- For example:

-- mmddyyyy

SET @d = 7022004

SELECT Convert(datetime, Stuff(Stuff( Right(Replicate('0',8) + Convert(varchar(10), @d),8) , 3, 0, '/'), 6, 0, '/'))

-- mmddyy

SET @d = 70204

SELECT Convert(datetime, Stuff(Stuff( Right(Replicate('0',6) + Convert(varchar(10), @d),6) , 3, 0, '/'), 6, 0, '/'))

So, if your date parameter (type datetime) is called @dt, then using the 'yyyymmdd' example, your WHERE clause would contain something like:

WHERE Convert(datetime, Convert(varchar(8), <your int date column> )) <= @dt - 90

rockmoose SSCarpal Tunnel Points: 4601 More actions · Answer 4

Hi,

You say that your date is stored as an int.

Ok does select convert(datetime,my_int_date) correspond to the actual date,

or is there some more logic involved ? like mkeast suggests.

In this example, I assume that

convert(datetime,my_int_date) = actual date...

and i convert the parameter to a number that can be checked against your int datecolumn:

declare @dateparam datetime

if @dateparam is null

set @dateparam = getdate() -- if no date provided use today...

-- select floor(convert(float,@dateparam))