convert condition - 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)

convert condition

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-10-01 : 02:05:51

convert(datetime,convert(varchar,EMRPatientInsuranceHistory.DATE_OF_CREATION,0)) = '01-OCT-2009 10:41:28 AM'

what is wrong with this?

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-10-01 : 02:09:15

Hi, see the below link

http://www.sql-server-helper.com/tips/date-formats.aspx

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-10-01 : 02:10:03

Hi, Can u explain the problem clearly ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 02:24:57

quote:
Originally posted by rajasekhar857

convert(datetime,convert(varchar,EMRPatientInsuranceHistory.DATE_OF_CREATION,0)) = '01-OCT-2009 10:41:28 AM'

what is wrong with this?

why converting to varchar and then back to date?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-01 : 02:29:04

quote:
Originally posted by rajasekhar857

convert(datetime,convert(varchar,EMRPatientInsuranceHistory.DATE_OF_CREATION,0)) = '01-OCT-2009 10:41:28 AM'

what is wrong with this?

The wrong part is you didnt specify what you want to do

Madhivanan

Failing to plan is Planning to fail

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-10-01 : 02:35:46

give me the correct one which works then please

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-10-01 : 02:36:32

SELECT EMRInsuranceTypesLkup.INSURANCE_TYPE, EMRPatientInsuranceHistory.DATE_OF_CREATION,

EMRVWINSURANCELIST.INSURANCE_PAYER_ID, EMRPatientInsuranceHistory.PATIENT_ID,

EMRPatientInsuranceHistory.INSURANCE_TYPE_ID, EMRVWINSURANCELIST.INSURANCE_NAME, EMRVWINSURANCELIST.INSURANCE_PHONE,

EMRPatientInsuranceHistory.INSURANCE_ID, EMRPatientInsuranceHistory.INSURANCE_GROUP,

EMRPatientInsuranceHistory.EMPLOYER, EPID.INSURANCE_SSN, EMRRelationsshipLkup.RELATIONSHIP_NAME,

EMRPatientInsuranceHistory.MEDICAID_NUMBER,EMRPatientInsuranceHistory.EMPLOYER_PHONE,

EMRPatientInsuranceHistory.INSURANCE_PROGRAM, EMRPatientInsuranceHistory.MEDICARE_NUMBER,

EMRPatientInsuranceHistory.INSURANCE_CARD, EMRPatientInsuranceHistory.INSURANCE_COPAY,

EMRPatientInsuranceHistory.GUARANTOR_ID, EMRVWINSURANCELIST.INSURANCE_ADDRESS, EMRVWINSURANCELIST.INSURANCE_CITY,

EMRVWINSURANCELIST.STATE_ID, EMRVWINSURANCELIST.STATE_CODE, EMRVWINSURANCELIST.INSURANCE_ZIP,

EMRPatientInsuranceHistory.POLICY_NUMBER, EMRPatientInsuranceHistory.PATIENT_INSURANCE_ID,EPID.FIRSTNAME_OF_INSURED,

EPID.LASTNAME_OF_INSURED,EPID.INSURANCE_DOB,EPID.INSURANCE_GENDER,EMRGenderLkUp.GENDER,

EMRVWINSURANCELIST.INSURANCE_MNEMONIC, EPID.INSURED_ADDRESS1,EPID.INSURED_ADDRESS2,EPID.INSURED_CITY,

EPID.INSURED_STATE, EPID.INSURED_ZIP,EPID.INSURED_PHONE,ESL.STATE_CODE AS INSURED_STATE_NAME,

EMRPatientInsuranceHistory.PAT_INSURED_ID

FROM EMRPatientInsuranceHistory

LEFT OUTER JOIN EMRInsuranceTypesLkup ON EMRPatientInsuranceHistory.INSURANCE_TYPE_ID = EMRInsuranceTypesLkup.INSURANCE_TYPE_ID

LEFT OUTER JOIN EMRRelationsshipLkup ON EMRPatientInsuranceHistory.RELATIONSHIP_TO_INSURED= EMRRelationsshipLkup.RELATIONSHIP_ID

LEFT OUTER JOIN EMRVWINSURANCELIST ON EMRPatientInsuranceHistory.INSURANCE_NAME_ID = EMRVWINSURANCELIST.INSURANCE_NAME_ID

LEFT OUTER JOIN EMRPatInsuredDetails EPID ON EPID.PAT_INSURED_ID = EMRPatientInsuranceHistory.PAT_INSURED_ID

LEFT OUTER JOIN EMRStatesLkup ESL ON EPID.INSURED_STATE = ESL.STATE_ID

LEFT OUTER JOIN EMRGenderLkup ON EPID.INSURANCE_GENDER = EMRGenderLkup.GENDER_ID

WHERE EMRPatientInsuranceHistory.PATIENT_ID = '1783'

AND

convert(datetime,convert(varchar,EMRPatientInsuranceHistory.DATE_OF_CREATION,0)) = '01-OCT-2009 10:41:28 AM'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 02:38:42

whats datatype of DATE_OF_CREATION? if its datetime, simply use

DATE_OF_CREATION='01-OCT-2009 10:41:28 AM'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-01 : 02:40:55

What are you trying to do with this?

convert(datetime,convert(varchar,EMRPatientInsuranceHistory.DATE_OF_CREATION,0)) = '01-OCT-2009 10:41:28 AM'

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 02:46:47

quote:
Originally posted by madhivanan

What are you trying to do with this?

convert(datetime,convert(varchar,EMRPatientInsuranceHistory.DATE_OF_CREATION,0)) = '01-OCT-2009 10:41:28 AM'

Madhivanan

Failing to plan is Planning to fail

i think what he needs is records created on a day, but he's using timepart also which wont give him what he's looking at. may be what he needs is this
EMRPatientInsuranceHistory.DATE_OF_CREATION >= '20091001'
AND EMRPatientInsuranceHistory.DATE_OF_CREATION<'20091002'

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-10-01 : 04:31:26

yous query is working for me fine but if i need using in the above format how can i achieve that as in my application format is like that.

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-10-01 : 04:35:48

thank you its working fine

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-01 : 04:45:49

welcome

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-01 : 04:49:58

quote:
Originally posted by rajasekhar857

thank you its working fine

As you see, you always give less information to help you
Why dont you give full informations at first place by clearly describing what you want instead of letting others to guess what you want?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources