Change the Date Format

Question

Change the Date Format

sachinrshetty

SSCrazy

Points: 2062
More actions
April 16, 2010 at 5:58 am

#233893

Hi
All,
I have declared a field CDATE as VARCHAR(15) in a temporary table. And I m displaying the value in that field in a report.
It is in mm/dd/yyyy format. Its datatype is varchar
Can I change that format to dd/mm/yyyy before displaying it in the SSRS.
Please post the answers.
Thank you

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

first, you should never store dates as any datatype except datetime.

fix it now so you don't have the same issue in the future; if it's stored as a date, you can convert it to any string format you want, or let the presentation layer do it as well.

that avoids this issue right up front.

if you can't convert the column, then

i would add a calculated column to your table.

then i'd convert the varchar to date and back to the desired format, or just do it inline in your select/update statement.

declare @val varchar(30)

set @val='04/16/2010'

select

convert(datetime,@val), --datetime,

convert(varchar(30),convert(datetime,@val),103) --103 format='16/04/2010'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Pulivarthi Sasidhar SSCertifiable Points: 6706 More actions · Answer 2

hi,

Please look into the following statements....

SELECT CONVERT(VARCHAR(12),GETDATE(),103) ---16/04/2010

SELECT CONVERT(VARCHAR(12),GETDATE(),101) ---04/16/2010

Convert the VARCHAR datatype to DATETIME and use 103 as in the above statements in the backend SP..

Sasidhar Chowdary

sachinrshetty SSCrazy Points: 2062 More actions · Answer 3

Thank you Lowell your logic really worked. Thank you so much. I was badly in need of this answer man.

Thank u again.

Sachin