Magic Date?

Anyone know why 1899-12-30 is a special date?
If you put that date with a time in sql server EM and a vb call will return only the time portion...
More background and sample code
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30709In vb, the date is a floating-point where the integer portion is the date. 12/30/1899 is the base date. So if you enter a time only, the integer portion of the datetime will be 0 and the decimal portion will be the time.|||What's EM Built in? If you just enter a time through EM, it'll ne 1899-12-30...

And isn't 1900-01-01 the 0 date for sql server?

SELECT CONVERT(datetime,0,101)

???

And why, if you call sql server from vb through ado, does it pass back just the time component...with no conversion function?|||I wonder if there is a magic time, that just returns the date...

Why the original developer didn't use CONVERT is betond me...|||Do a query against that datetime column that only has a time and add 1 to it - your question will be answered.|||SELECT CONVERT(datetime,-2,101)
gives '1899-12-30 00:00:00.000'|||Originally posted by rnealejr
Do a query against that datetime column that only has a time and add 1 to it - your question will be answered.

What does that mean?

Datetime is stored as a number...4 before the decimal, 4 after...

What do you mean time only?|||So what does this prove?

SELECT DATEADD(d,1,CONVERT(datetime,0.1))

Add 1 what?

What do you mean with no date? There's always a date component?

0 is the default

(Unless of course you add through EM then it's -2)

huh?

And why, if you make a sql call from vb, and the date is 1899-12-30, it only returns the time..VERY bizzare|||Just had the developer do it from an Excel workbook too.

It puts just the time in the Cell with that 1899-12-03 date

It doesn't return the date...bizzaro

Anyone else seen this?|||http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27101

Hey Brett ... some more with excel problems ...

I believe the reason is that while SQL takes the default date to be 1900-01-01

and other MS applications use 1899-12-30

Here is a link
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbadev/html/whatisdatehowdiditgetthere.asp|||Thanks...forgot all about that thread...

Still doesn't explain you only get the decimal portion of the datetime field though (that's the time component)

LTRIM in grouping

Hello All,

I am trying to ltrim a portion of multiple fields in a grouping. I am able to do it for one of them, but unfortunately there are several I have to do it for. If I use the following expression, it works for that one.

Code Snippet

=iif(Fields!BankNumber.Value="083"and Fields!TestName.Value="Inquiry Menu - Bank 083",LTRIM("Inquiry Menu"),Fields!TestName.Value)

However, if I try and do it for more than one it errors out. For example...

Code Snippet

=iif(Fields!BankNumber.Value="083"and Fields!TestName.Value="Inquiry Menu - Bank 083",LTRIM("Inquiry Menu"),Fields!TestName.Value)

OR iif(Fields!BankNumber.Value="083"and Fields!TestName.Value="Search Menu - Bank 083",LTRIM("Search Menu"),Fields!TestName.Value)

OR iif(Fields!BankNumber.Value="083"and Fields!TestName.Value="SEAX - Bank 083",LTRIM("SEAX"),Fields!TestName.Value)

Is there another way to arrange this so I can LTRIM each field group seperately?

Thanks,

Clint

It is not clear what exactly you are trying to do, but I'll take a crack at it. If I miss the mark, point me in the right direction.

I think that what you want is to remove the " - Bank 083" string from the end of your testname when the banknumber =083

The expression that you wrote does not exactly do that, and anyway there's a simpler way:

=Replace(Fields!TestName.Value, " - Bank 083", "")

What this does is return the testname with any instance of " - Bank 083" replaced with nothing.

What you wrote (in the first case) says "If the banknumber is 083 and the testname is "Inquiry Menu - Bank 083" then use the string "Inquiry Menu" with no leading spaces, otherwise use the testname.

So the value of that expression is either a literal string, or testname, which is also a string. You got a syntax error because there is no meaning to the expression "string1" OR "string2"

I took a few liberties in assuming characteristics of your data with the solution I offered. Specifically, I assume that only banknumber 083 has " - Bank 083" at the end of the testname.

You only mention the case of this one bank... Are all the other values for testname formatted correctly? If not, you'll probably want to make a more general solution using InStr and SubStr