select query problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

select query problem

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-04-13 : 05:11:39

query like this

SELECT ltrim(rtrim(convert(varchar,CREATED_DATE,105))) + ' ' +
rtrim(ltrim(replace(replace(substring(convert(varchar,CREATED_DATE,0),13,7),'AM',' AM'),'PM',' PM')))
AS "Encounter Date" ,cast(TOTAL_HEIGHT as numeric(20,2)) "Height",cast(TEMPERATURE as numeric(20,2))
"Temperature", cast(OXYGEN_SATURATION as numeric(20,2)) "Sa02",cast(TOTAL_WEIGHT as numeric(20,2)) "Weight",
cast(PULSE as numeric(20,2)) "Pulse",cast(SYSTOLIC_BP as numeric(20,2)) "Systolic BP",
cast(DIASTOLIC_BP as numeric(20,2)) "Diastolic BP",cast(BMI as numeric(20,2)) BMI ,
CREATED_DATE as ENC_DATE,ENCOUNTER_ID, patient_id
from EMREncounterDetails
WHERE
IS_SAVE_FOR_LATER IN (0,4) AND
PATIENT_ID = '21013' AND
( CAST(FLOOR(CAST(CREATED_DATE AS float)) AS datetime) between convert(datetime,CREATED_DATE,113)
AND convert(datetime,CREATED_DATE,113) )

getting column ENC_DATE result as

2010-01-04 00:00:00.000

but it shold be like

2010-01-04 11:42:00.000

WHAT NEEDS TO CHANGE

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-13 : 05:16:15

quote:

cast(DIASTOLIC_BP as numeric(20,2)) "Diastolic BP",cast(BMI as numeric(20,2)) BMI ,
CREATED_DATE as ENC_DATE,ENCOUNTER_ID, patient_id
from EMREncounterDetails

the ENC_DATE is column CREATED_DATE from your table EMREncounterDetails. Does that column contain the time ?

quote:
( CAST(FLOOR(CAST(CREATED_DATE AS float)) AS datetime) between convert(datetime,CREATED_DATE,113)
AND convert(datetime,CREATED_DATE,113) )

what is this for ?

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-13 : 05:17:38

In your well formatted select we can see:
CREATED_DATE as ENC_DATE
So there we can see that this date is coming as it is - no convert or anything else.
Means: this is the value stored in the table like this.

No, you're never too old to Yak'n'Roll if you're too young to die.

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-04-13 : 05:19:02

its a datetime field and records are some what like this

2010-04-12 18:30:00.000
2010-04-12 18:32:00.000
2010-04-12 19:16:00.000
2010-04-13 10:40:00.000
2010-04-13 10:42:00.000
2010-04-13 10:49:00.000
2010-04-13 11:42:00.000
2010-04-13 11:45:00.000

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-13 : 05:19:44

ENC_DATE is the raw CREATED_DATE column from EMREncounterDetails?

What datatype is this column? You seem to be converting everything to different things?

To be brutally honest, your data looks like it will be a complete mess. Why so many conversions?

This line doesn't make any sense to me at all:


AND
( CAST(FLOOR(CAST(CREATED_DATE AS float)) AS datetime) between convert(datetime,CREATED_DATE,113) 
AND convert(datetime,CREATED_DATE,113) )

You are checking that a column - converted to a float and then to a datetime is between two identical values? (both BETWEEN options are convert(datetime,CREATED_DATE,113))

I think you should consider scrapping this query and focus on:

what you want to achieve

and not what you have so far.

If you post the table structure, some sample data and your required results you'll get a much nicer solution than this.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-04-13 : 05:22:12

what i need to do then

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2010-04-13 : 05:31:08

for me where conition failing what i need to place there

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-13 : 05:32:11

remove that last condition and try

"( CAST(FLOOR(CAST(CREATED_DATE AS float)) AS datetime) between convert(datetime,CREATED_DATE,113)
AND convert(datetime,CREATED_DATE,113) )"

not sure what is your intention of this but it does not make sense at all.
the cast-floor-cast will truncate off the time portion of the CREATE_DATE and change to 00:00:00
and checking it BETWEEN A and A will probably give you the record where CREATE_DATE is at 00:00:00

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources