patindex in datetime field

Question

Post reply

patindex in datetime field

Angelindiego

SSCertifiable

Points: 6048
More actions
August 8, 2007 at 10:17 am

#125266

ok everyone, I need help!! Here is my dilema:
I have 2 columns. LabDate, LabTime. Sometimes there is no 'time' in the LabDate column. If that is the case, then I need to concatenate the LabTime column to the LabDate column.
What I need to do is test for if there is NO labtime in the LabDate column. Now, in my db, it doesn't show a labtime, but in running a query, it does show '00:00:00.00'. My thoughts were to use patindex and search for a colon, but that won't work. I need to query for the timestamp being > 00:00. Got any ideas for me?? I am migrating data from one db to another and need to do a select from a staging table on those 2 columns and insert the full LabDateTime into another table.
thank you for any help...as always!! You guys rock!
Thank you!!,
Angelindiego

Viewing 10 posts - 1 through 9 (of 9 total)

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

Sql Junkie-204042 Hall of Fame Points: 3874 More actions · Answer 1

What are the datatypes of the 2 columns ? Can you include some sample data ?

Angelindiego SSCertifiable Points: 6048 More actions · Answer 2

In my staging table, the LabDate is a datetime field and the LabTime is a varchar(50) field.

LabDate = 6/11/2004 or 1/21/2007 12:04:00 AM

LabTime = 9:40:24 PM

...thanks!

Thank you!!,

Angelindiego

Sql Junkie-204042 Hall of Fame Points: 3874 More actions · Answer 3

Check out if this helps you.

DECLARE @dt datetime, @time VARCHAR(20)

SET @dt = '2007-08-08 12:01 AM'

SET @time = '9:40:24 pm'

SELECT CASE WHEN @dt <> DATEADD(day,0,DATEDIFF(day,CAST(0 AS datetime), @dt))

THEN @dt

ELSE

CASE WHEN ISDATE(@time) = 1 THEN

DATEADD(s, DATEDIFF(s, CAST(0 AS DATETIME), CAST(@time as datetime)), @dt)

ELSE

@dt

END

Angelindiego SSCertifiable Points: 6048 More actions · Answer 4

OK!! in a quick test this WORKS!!~~~~ I will implement this into my code and test again. I can't thank you enough!!!!!

Thank you!!,

Angelindiego

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Angelindiego,

I posted it just several hours ago.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=388102

But I see the number of "programmers" not having any clue and (here is the problem) not bothering to RTFM is no that small.

So, I repeat:

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

Datetime field does not have any format.

It's not a string, so there not gonna be any use for any string function.

In fact datetime is FLOAT value reflecting number of days (including fraction) passed since the beginning of year 1900.

Whatever string representation of datetime you see is just a result of conversion performed by front-end application.

Different applications do it differently. That's why same datetime value will appear in different formats in EM and QA.

And nothing should stop you from setting up your own datetime representation in you own front-end application according to the requirements you've got.

_____________
Code for TallyGenerator

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 6

DATETIME is a FLOAT? Since when?

I thought DATETIME was a 64-bit binary integer value, where the lowest four bytes is the data portion, and the higher 4 bytes is the time portion...

N 56°04'39.16"
E 12°55'05.25"

Sergiy SSC Guru Points: 110208 More actions · Answer 7

It's what BOL says.

Unfortunately it's not true.

DATETIME is not exactly FLOAT.

It's "FLOAT with constraints"

_____________
Code for TallyGenerator

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 8

declare

@a datetime,

@b-2 datetime,

@C datetime

select

@a = '20070820',

@b-2 = '19000101 18:00:00',

@C = '20070820 10:00:00'

select

cast(@a as binary(8)),

cast(@b-2 as binary(8)),

cast(@C as binary(8))

N 56°04'39.16"
E 12°55'05.25"

Jeff Moden SSC Guru Points: 1004439 More actions · Answer 9

I agree...BOL is incorrect or, at the very least, misleading here... if you use any decimal math against the time portion, you will many times come up with the wrong answer. Changing the datatypes of the variables involved to float will produce the correct answers.

Internally, the data is actually stored as if it were a fixed length FLOAT... that is, it has a mantissa (significand) but no exponent. If you split the mantissa between the date and the time, yes, it looks like two integers but unless you're willing to do machine level byte slicing, SQL server treats it as a fixed length float (Serqiy called it "Float with constraints) because the time portion (decimal portion) is calculated at a binary level, making it the same as a float.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)