datetime format issue 1

[clyde11]

Hi all,
I have a view with a datetime column,
when viewing the column data the format is:

" 2006-06-12T12:00:00.0000000+03:00 "

while i'm only want to see the date part

(that is, 2006-06-12, and if possible in the format: dd/mm/yyyy)
now, when i'm using convert( varchar(10), [column_name],101)
i'm getting the format i need but the problem is that i'm
"losing" the date type, is it possible to view it as the
format i stated and still as a datetime type ??

Regards,
Pit

 

[bborissov]

No,
SQL Server has no Date type, you must resolve this issue in Fron End, not on SQL Server side.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[SQLBill]

Are you sure the column is a DATETIME data type? Your example isn't a SQL Server date and time format. Could it be a VARCHAR column instead? Or are you using some other database system?

-SQLBill

Posting advice: FAQ481-4875

 

[clyde11]

yes, it is a sqlserver datetime, i guees you are excepcting the "2006-06-12 12:00:00" format,
but still, assuming i want only the "2006-06-12 " part
being displayed and still maintain datetype type
is it possible ?

Thanks
P.

 

[gmmastros]

Please read this faq183-5842 (especially part 7).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mutley1]

I suspect it may be the below in the view.

convert (varchar, mydatefield, 126)

If (as SQLBill suggests) it is a varchar column using a convert then you will want a 105 conversion for the format you want.

Post the statement that produces the view and it will be easier to work out.

Cheers,

M.

 

[SQLBill]

Good catch Mutley1. I rechecked my CONVERSION sheet (from BOL) and found the 126 (ISO8601) format.

That supports my thought that it is a VARCHAR field, or that the query is converting it to VARCHAR.

-SQLBill

Posting advice: FAQ481-4875

 

[mutley1]

It was nagging in the back of my mind about the capital T in the output. It must be a varchar convert in the view.

Clyde - if you need to check what output format you want, run this in QA and it will give you all the different conversion formats for a date / date time output.

select
    convert(varchar, GetDate(), 100) as '100 Conversion',
    convert(varchar, GetDate(), 101) as '101 Conversion',
    convert(varchar, GetDate(), 102) as '102 Conversion',
    convert(varchar, GetDate(), 103) as '103 Conversion',
    convert(varchar, GetDate(), 104) as '104 Conversion',
    convert(varchar, GetDate(), 105) as '105 Conversion',
    convert(varchar, GetDate(), 106) as '106 Conversion',
    convert(varchar, GetDate(), 107) as '107 Conversion',
    convert(varchar, GetDate(), 108) as '108 Conversion',
    convert(varchar, GetDate(), 109) as '109 Conversion',
    convert(varchar, GetDate(), 110) as '110 Conversion',
    convert(varchar, GetDate(), 111) as '111 Conversion',
    convert(varchar, GetDate(), 112) as '112 Conversion',
    convert(varchar, GetDate(), 113) as '113 Conversion',
    convert(varchar, GetDate(), 114) as '114 Conversion',
    convert(varchar, GetDate(), 120) as '120 Conversion',
    convert(varchar, GetDate(), 121) as '121 Conversion',
    convert(varchar, GetDate(), 126) as '126 Conversion',
    convert(varchar, GetDate(), 130) as '130 Conversion',
    convert(varchar, GetDate(), 131) as '132 Conversion'

Cheers,

M.

 

[mutley1]

Sorry SQLBill......OR a varchar field being pulled to the view!!!

 

[clyde11]

Guys,
Thank you very much, that stuff helped a lot
P.

 

[mutley1]

You're welcome