cast string to date time - 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 2008 Forums

Transact-SQL (2008)

cast string to date time

Author

Topic

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-03-15 : 15:43:28

i have a temp table that i need the date which is currently a string to be a datetime

CAST(INIT_DATE_OF_RQST AS datetime) ,
INIT_NOTE_DATE varchar(255),
INIT_NOTE_TIME varchar(255),

do i do that when i create the table
or in the select statment?

what is the correct syntax i keep getting an error
Msg 173, Level 15, State 1, Procedure NYP_PAL_CARE_rpt, Line 68
The definition for column 'CAST' must include a data type.

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-15 : 16:07:18

you're gonna have to post more code....

and did you know you ave a cool logon...my... Sharona?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-03-15 : 16:15:34

thanks its a nickname, my real name is sharon
IN RED AND BLUE
not really sure where it belongs.

CREATE TABLE #FINAL
(
Site varchar(10),
Campus varchar(30),
ClientDocGUID numeric(16,0),
ClientVisitGUID numeric(16,0),
PatCareDocGUID numeric(16,0),
MRN varchar(20),
EncNumber varchar(20),
PATIENT varchar(50),
DOB DATETIME,
GENDER VARCHAR(15),
LANGUAGECDE varchar(20),
ADMIT_DATE Datetime,
DISCHARGE_DATE DATETIME,
Unit_Note_Written VARCHAR(30),
LOC_time_rpt varchar(30),
LOC_NAME VARCHAR(60),
--cat_name varchar(60),
--Value_text VARCHAR(2000),
Prim_diag_description VARCHAR(60),
Username varchar(50),
User_Occupation varchar(30),
INIT_DATE_OF_RQST varchar(255),
INIT_NOTE_DATE varchar(255),
INIT_NOTE_TIME varchar(255),
REFERRING_PROVIDER varchar(255),
REFERRING_SERVICE varchar(2000),
INIT_SUPPORT_COUNSELING varchar(2000),
INIT_FAMILY_SUPPORT VARCHAR(2000),
INIT_HOSPTICE_REF_DISCUSS VARCHAR(2000),
INIT_PROGNOSTICATION VARCHAR(2000),
INIT_PAIN_MGMT VARCHAR(2000),
INIT_SYMPTOM_MGMT VARCHAR(2000),
INIT_WITHHOLD_SUSTAIN VARCHAR(2000),
INIT_DISCHARGE_PLAN VARCHAR(2000),
INIT_GOALS_CARE VARCHAR(2000),
INIT_ADVANCE_DIRECTIVE VARCHAR(2000),
INIT_PATIENT_SUPPORT VARCHAR(2000),
INIT_OTHER VARCHAR(2000),
FLUP_PAIN_MGMT VARCHAR(2000),
FLUP_SYMPTOM_MGMT VARCHAR(2000),
FLUP_DECISION_MAKING VARCHAR(2000),
FLUP_GOALS_CARE VARCHAR(2000),
FLUP_PAT_FAM_SUP VARCHAR(2000),
FLUP_PROGNOSIS VARCHAR(2000),
FLUP_WITHDRAWAL_LIFE VARCHAR(2000),
FLUP_DISCHARGE_PLAN VARCHAR(2000),
FLUP_HLTH_CARE_AGENT VARCHAR(2000),
FLUP_ADVANCE_DIRECTIVE VARCHAR(2000),
FLUP_HOSPTICE_REF_DISCUSS VARCHAR(2000),
FLUP_EDU_SUP_STAFF VARCHAR(2000),
FLUP_FAM_CONF VARCHAR(2000),
FLUP_CODE_STATUS VARCHAR(2000),
FLUP_HOSPTICE_REF VARCHAR(2000))

--Get all patients who had a PALLIATIVE CARE CONSULT Note within the select date time
SELECT
Site = 'WEST',
Campus =
CASE
when loc.ParentGUID = 2000001000061001 then 'Allen Hospital'
when loc.ParentGUID = 4000001145061001 then 'Milstein Hospital'
when loc.ParentGUID = 8000001069061001 then 'MS CHONY'
END,
cd.GUID as ClientDocGUID,
cv.GUID AS ClientVisitGUID,
cd.PatCareDocGUID ,
cv.IDCode AS MRN,
cv.VisitIDCode AS EncNumber,
cv.ClientDisplayName AS PATIENT,
CASE WHEN ISDATE (rtrim(convert(char(2),c.BirthMonthNum))+'/'+ rtrim(convert(char(2),c.BirthDayNum))+'/'+
rtrim(convert(char(4),c.BirthYearNum))) =1 THEN
convert(char(2),c.BirthMonthNum) +'/'+ rtrim(convert(char(2),c.BirthDayNum))+'/'+
convert(char(4),c.BirthYearNum) ELSE null end DOB,
c.gendercode AS GENDER,
c.languagecode AS LANGUAGECDE,
cv.AdmitDtm AS ADMIT_DATE,
cv.DischargeDtm AS DISCHARGE_DATE,
Unit_Note_Written= DBO.nyp_TF_GetLocationForEvent (clientVisitGUID,obserdoc.RecordedDtm),
CV.CurrentLocation AS LOC_TIME_RPT,
LOC.NAME AS LOC_NAME,
obsCatalog.Name as cat_name,
CASE WHEN obsCatalog.Name = 'PalCare_InitCons_ReqDate_FT'
THEN convert(varchar, ISNULL(obser.ValueText, obsFS.Value), 112)as DATETIME END AS INIT_DATE_OF_RQST ,
ISNULL(obser.ValueText, obsFS.Value) AS Value_TEXT,
dbo.CV3Service.Description as Prim_diag_description,
dbo.CV3User.displayname as Username,
dbo.CV3User.OccupationCode as User_occupation

INTO #RESULT
FROM dbo.CV3ClientVisit AS cv INNER JOIN
dbo.CV3Location AS loc ON cv.CurrentLocationGUID = loc.GUID INNER JOIN
dbo.CV3ClientDocument AS cd ON cv.GUID = cd.ClientVisitGUID INNER JOIN
dbo.CV3ObservationDocument AS obserdoc ON cd.GUID = obserdoc.OwnerGUID INNER JOIN
dbo.CV3Observation AS obser ON obser.GUID = obserdoc.ObservationGUID INNER JOIN
dbo.CV3ObsCatalogItem AS obscatitem ON obscatitem.GUID = obser.ObsItemGUID INNER JOIN
dbo.CV3ObsCatalogMasterItem AS obsCatalog ON obsCatalog.GUID = obscatitem.MasterObsGUID INNER JOIN
dbo.CV3Client as c ON cv.ClientGUID = c.GUID INNER JOIN
dbo.CV3Service ON cv.ServiceGUID = dbo.CV3Service.GUID INNER JOIN
dbo.CV3User ON cd.UserGUID = dbo.CV3User.GUID LEFT OUTER JOIN
dbo.SCMObsFSListValues AS obsFS ON obsFS.ParentGUID = obserdoc.ObservationDocumentGUID
WHERE cd.PatCareDocGUID IN ('9000001693102020', '9000001693202020')
---(cd.PatCareDocGUID IN ('494102020', '470102020')) -dev guids
--AND loc.ParentGUID in('2000001000061001','4000001145061001 ','8000001069061001')
AND cv.TypeCode = 'Inpatient' and cv.IDCode ='127 85 67' and cv.VisitIDCode='000056334 735'
--AND cd.AuthoredDtm BETWEEN @start_date AND @end_date

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-15 : 16:48:24

sharon,

I know you get an error on the creat

varchar(2000) for n columns?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-15 : 16:51:00

can you cut and paste the insert into code into it's own query window to see if thats the prob?

get simple

and why do people suffix their sprocs instead of prefic them?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-03-16 : 07:41:56

no i dont get an error on that. i have had no issues until i am
trying to cast the red items to a date from a varchar.

quote:
Originally posted by X002548

sharon,

I know you get an error on the creat

varchar(2000) for n columns?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

sharona
Yak Posting Veteran

75 Posts

Posted - 2011-03-16 : 09:16:48

in my final select statment i did this.

INIT_DATE_OF_RQST=CAST( CAST(INIT_DATE_OF_RQST as varchar(255)) AS datetime),
INIT_NOTE_DATE=CAST( CAST(INIT_NOTE_DATE as varchar(255)) AS datetime),

which converted perfectly.
thanks have a nice day. happy almost st. pats.!!!

X002548
Not Just a Number

15586 Posts

Posted - 2011-03-16 : 14:26:47

problem comes in when you have a value that does not convert

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources