Date Conversion Confusion

[antiskeptic]

I am writing a stored proc with a few dates in the select statement (Using SQL Server 2000 / Query Analyzer).
Only problem is, the dates aren't really date fields. They are integers of 4 digit numbers that I have to go through a conversion to get the date I want. And the formula I have below takes care of it beautifully.

convert(datetime,(dateadd(day,employee_pay_period.Pay_Period_Ending_Date,'1/1/1930')))Pay_Period_End_Date

My problem comes when the integer format changes for a different field...giving me another type of date. This one is an 8 digit number rather than four, and is calculated in minutes rather than days. I tried this, thinking it would do the trick:

substring(convert( varchar, dateadd( mi, employee_pay_period.employee_signature_timestamp, 
convert( datetime, '01/01/1930' ) ), 100 ), 13,6)

but it only gives me the time...not the day. Would anyone happen to know how I could get the date and time out of this? And if not time, just date...that is what I really need here. I'm goin nuts! *ha*

Thank you in advance for any help you can provide.

Sincerely,
Antiskeptic

 

[SQLBill]

Can you provide one or two samples of what the data looks like and what it equates to?

Value Date Conversion


-SQLBill

 

[antiskeptic]

it is an eight digit number like:

39235188

that represents the number of MINUTES since 1/1/1930...don't ask why...I don't understand it either, but that's what it is. And I have no position to question it...only to make it work.

and the code I used to make a date is:

substring(convert( varchar, dateadd( mi, employee_pay_period.employee_signature_timestamp, 
convert( datetime, '01/01/1930' ) ), 100 ), 13,6)

but it turns it into this:
3:48P

The other type of date field is a 4 digit number that represents the number of DAYS since 1/1/1930..this one I use this code:

convert(datetime,(dateadd(day,employee_pay_period.Pay_Period_Ending_Date,'1/1/1930')))

and the outcome is:
2004-08-06 00:00:00.000

This second one is the format I need...but I need it from the MINUTES field...I get an overflow error when I try to use above code for the DAYS field.
Hope I haven't confused you further. I'm trying to be aspecific as I can.

THank you,
Brenda

 

[SQLBill]

You didn't confuse me further. The explanation was clear, your initial post used the 1/1/1930 but never said that was where the minutes were counted from.

Stupid me....I was overlooking the obvious...it's your SUBSTRING causing the problem. Test this:

DECLARE @mydate DATETIME
SET @mydate = '2004-08-06 12:34:56.000'
SELECT (CONVERT(VARCHAR, @mydate, 120)
SELECT SUBSTRING(CONVERT(VARCHAR, @mydate, 120), 13, 6)
SELECT SUBSTRING(CONVERT(VARCHAR, @mydate, 120), 1, 10)

You 'grab' the wrong part of the datetime string. The above script will return:
1. The converted string
2. The substring as you were returning it
3. The substring as it should be returned.

By the way, you need to use CONVERT style 120 or 121.

-SQLBill

 

[antiskeptic]

well, I'm afraid I must not have explained my self very good again.

The script you gave me, does what I need, only in reverse. Your @mydate needs to be the 8 digit number (counting the minutes from 1/1/1930) not the date format...The way your above script does it,
"3. The substring as it should be returned."...is returning the 8 digit number I am starting with. I don't have a date formatted field to start with.

does this make sense? I'm so bad trying to explain this stuff in email. *ugh*

Thank you,
Antiskeptic

 

[SQLBill]

What happens when you run this:

substring(convert( varchar, dateadd( mi, employee_pay_period.employee_signature_timestamp, 
convert( datetime, '01/01/1930' ) ), 120 ), 1,10)

-SQLBill

 

[SQLBill]

By the way, I ran this:

substring(convert( varchar, dateadd( mi, 39235188, 
convert( datetime, '01/01/1930' ) ), 120 ), 1,10)

and my result was 2004-08-06.

My first code was just to show you how the substring command worked. If you had looked at it and compared it to your code (which was what I was trying to get you to do); you would see that the datetime was being returned (no matter which way it was done) as 2004-08-06 12:34:56.000. Then the substring that YOU use 'grabs' the part beginning at the 13th character which is the beginning of the time. You need to grab it starting at the 1st character.

in other words, this is the result BEFORE the substring:
2004-08-06 12:34:56.000
12345678911111111112222
01234567890123
^^^^^^ part you get with your substring
^^^^^^^^^^ part I get with my substring

Does that make it clear?

-SQLBill

 

[antiskeptic]

yes, much more clear. Thank you...I misunderstood the first go round. And I DID compare it to my original code...what you sent was not completely in vain. It was just lost in translation, sorry.
But, yes, that is exactly what I needed, and I think I now understand what you meant by the substring problem I was having. I get it now. Thank you a million, and sorry I didn't catch right on.

Sincerely,
Antiskeptic

 

[SQLBill]

That's okay. Just remember that when you need to debug a script and it has several nested commands, try to test each one separately. Testing this:

SELECT convert( varchar, dateadd( mi, 39235188, 
convert( datetime, '01/01/1930' )

would have shown you the conversion from the integer to datetime worked. Then you would have to get only the portion you needed, which was the rest of the script.

The problem was that I skipped explaining all that and just got to the heart of the problem - which was the substring.

Have fun...

-SQLBill