How to rid of question mark in cast function.?

Question

How to rid of question mark in cast function.?

Virtual Reality

Ten Centuries

Points: 1182
More actions
April 12, 2016 at 2:27 pm

#306365

Hello,
I have begin_date as datetime datatype in sql server.
I am trying to create report.
In that, I just want to show only date as begin_dte. So I used cast function.
cast(begin_dte as date).
Somehow, I am getting question mark in place of time in output.
How could I rid of question mark in cast function?
Do you have this type of situation before?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

have you got an example of a datetime that is creating this problem?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Phil Parkin SSC Guru Points: 246978 More actions · Answer 2

RV16 (4/12/2016)
Hello,
I have begin_date as datetime datatype in sql server.
I am trying to create report.
In that, I just want to show only date as begin_dte. So I used cast function.
cast(begin_dte as date).
Somehow, I am getting question mark in place of time in output.
How could I rid of question mark in cast function?
Do you have this type of situation before?
Thanks

Please provide some sample SQL to exemplify what is going for others.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Virtual Reality Ten Centuries Points: 1182 More actions · Answer 3

datetime with cast function creating this problem.

SELECT section_master_v.yr_cde,

section_master_v.trm_cde,

section_master_v.crs_title,

section_master_v.credit_hrs,

section_master_v.crs_cde,

section_master_v.crs_comp3,

section_master_v.crs_comp1,

section_master_v.loc_cde,

section_schedules_v.bldg_cde,

section_schedules_v.room_cde,

Cast(section_schedules_v.begin_dte As date) 'B Date',

Cast(section_schedules_v.end_dte As Date) 'E Date',

cast(section_schedules_v.begin_tim As time) 'B Time',

Cast(section_schedules_v.end_tim As time) 'E Time',

section_master_v.lead_instructr_id,

name_master.first_name,

name_master.last_name

FROM section_master_v,

section_schedules_v,

name_master

WHERE ( section_master_v.crs_cde = section_schedules_v.crs_cde ) and

( section_master_v.yr_cde = section_schedules_v.yr_cde ) and

( section_master_v.trm_cde = section_schedules_v.trm_cde ) and

( name_master.id_num = section_master_v.lead_instructr_id ) and

( section_master_v.CRS_COMP3 < =10 )

output would be:

1/19/2016 ?? ??? ?????

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 4

RV16 (4/12/2016)
output would be:
1/19/2016 ?? ??? ?????

ok...so thats the output....what was the "input" for this result....(you have said that it is datetime datatype)

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Okay, can you recreate the problem in an empty database with a subset of the data?

Virtual Reality Ten Centuries Points: 1182 More actions · Answer 6

Hello,

I could solve this error message using convert and mid function. I am not using cast but I convert datetime datatype to nvarchar and then just use mid function with specified length.

Now I am not getting question mark in place of time.

I am also attaching code here for better understanding

SELECT section_master_v.yr_cde,

section_master_v.trm_cde,

section_master_v.crs_title,

section_master_v.credit_hrs,

section_master_v.crs_cde,

section_master_v.crs_comp3,

section_master_v.crs_comp1,

section_master_v.loc_cde,

section_schedules_v.bldg_cde,

section_schedules_v.room_cde,

convert(nvarchar,SECTION_SCHEDULES_V.BEGIN_DTE,120) 'B Date',

convert(nvarchar,section_schedules_v.end_dte,120) 'E Date',

convert(nvarchar,section_schedules_v.begin_tim,120) 'B Time',

convert(nvarchar,section_schedules_v.end_tim,120) 'E Time',

section_master_v.lead_instructr_id,

name_master.first_name,

name_master.last_name

FROM section_master_v,

section_schedules_v,

name_master

WHERE ( section_master_v.crs_cde = section_schedules_v.crs_cde ) and

( section_master_v.yr_cde = section_schedules_v.yr_cde ) and

( section_master_v.trm_cde = section_schedules_v.trm_cde ) and

( name_master.id_num = section_master_v.lead_instructr_id ) and

( ( section_master_v.trm_cde = :trm_cde ) AND

( section_master_v.yr_cde = :yr_cde ) AND

( section_master_v.CRS_COMP3 < =10 ) )

and then I used mid( b_date , 0, 11) ,mid( e_date , 0, 11 ), mid(b_time,11,24) , mid(e_time,11,24) in reporting tool.

Thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

I am glad you resolved your problem, but it doesn't answer the question.

Run the following code, there are no issues:

declare @StartDate datetime = '2016-01-01 13:00:00.000';

declare @StartDateN nvarchar(30);

set @StartDateN = convert(nvarchar(30),@StartDate,121);

select @StartDate StartDateTime, cast(@StartDate as date) StartDate, cast(@StartDate as time(3)) as StartTime;

select @StartDateN, cast(@StartDateN as date), cast(@StartDateN as time(3));

The problem we have helping you is that we can't see what you see. All you showed was the code, no DDL for the tables and no sample data that would allow us to duplicate the problem.