Date format conversion question

Question

Date format conversion question

sfletcher-933270

Right there with Babe

Points: 784
More actions
September 30, 2009 at 10:04 pm

#205224

I am trying to run a simple convert statement for use in a stored procedure. I just want to display some date data (data type = varchar) in the format dd Mon yyyy. I can convert satisfactorily when using getdate() as a test, but not on my field (OA.ApprovalISODate2). Can you please explain to what is wrong with my convert statement?
selectconvert(varchar(10),OA.ApprovalISODate2, 106),
convert(varchar(10),getdate(), 106)
fromObjAccession OA
Results as below:
OA.ApprovalISODate2
1988-01-28
Getdate
01 Oct 200

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

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

sfletcher-933270 (9/30/2009)
I am trying to run a simple convert statement for use in a stored procedure. I just want to display some date data (data type = varchar) in the format dd Mon yyyy. I can convert satisfactorily when using getdate() as a test, but not on my field (OA.ApprovalISODate2). Can you please explain to what is wrong with my convert statement?
selectconvert(varchar(10),OA.ApprovalISODate2, 106),
convert(varchar(10),getdate(), 106)
fromObjAccession OA
Results as below:
OA.ApprovalISODate2
1988-01-28
Getdate
01 Oct 200

Yes... you must first cast your VARCHAR date to datetime and then the CONVERT will work. As a side bar, this is the very reason why you should not store dates in tables as VARCHARS.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sfletcher-933270 Right there with Babe Points: 784 More actions · Answer 2

Hi Jeff

Thanks very much for your help. I thought it was something quite simple, but couldn't get my head around it. Have used the cast convert statement and get the results that I need. Point taken re should not store dates in tables as VARCHARS, but I am working with a purchased database and we are stuck with the given structure.

kind regards, Sally

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

I kind of figured it was something like that. I've run into that type of problem with 3rd party vendors more than once.

Anyway... glad it worked out and thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pandeharsh Ten Centuries Points: 1106 More actions · Answer 4

Hi,

how is the date column stored in table,dd/mm/yyyy or mm/dd/yyyy.

convert varchar to datetime,not directly.

Make seperate table with same structure,only change will varchar to datetime column.

Then transfer the whole data to new table via script.

Worst scenario is when varchar column contain date in dd/mm/yyyy,manipulation is very difficult.

[font="Verdana"]Regards
Kumar Harsh[/font]

:unsure:

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

I just had a thought about this, Sally... can you add a calculated column to the table without it breaking the 3rd part app?

Another possible "trick" would be to make a view of the table with the conversion already done in the view just to make future programming for you folks a little easier.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sfletcher-933270 Right there with Babe Points: 784 More actions · Answer 6

Hi Jeff

Thanks for this suggestion. I will make a view with this conversion and a couple of other similar ones so that I don't have to rack my brains again on this particular issue.

Kind regards, Sally

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

Thanks for the feedback, Sally.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)