convert date format

How do i convert this date into sql server 2005 datetime format which is dd/MM/yyyy

while i am getting this date result

21:53:20 Jul 09, 2009 PDT

thank you,
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
This should help you out.

convert(datetime, ur_datetime_column, 103)
Try using this:
CONVERT(VARCHAR(10), Your_Field, 103)
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> convert(datetime, ur_datetime_column, 103)

Typed in a hurry and it should be
convert(CHAR(10), ur_datetime_column, 103)
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Your database system should be storing things as the datetime type. The rest is just formatting.

If you're querying the table and it's coming out as "21:53:20 Jul 09, 2009 PDT", then don't worry - let whatever's asking do the reformatting (for example a VB.Net application, SSRS report, whatever).

Handle dates as dates. Strings as strings. Don't confuse the two, and only convert a date into a string when it's time to display it.

Mark WillsTopic AdvisorCommented:
There is no "style" code that will handle a string conversion of  time + date + PDT  (the PDT being time zone)

Not sure where you are getting that from...  "while I am getting this date result" getting it from where ? Is it a variable ? A column in the database ? What datatype is the column ?

To cast as a datetime, this will work : select convert(datetime,replace('21:53:20 Jul 09, 2009 PDT','PDT','') )  

Once it is a "recognised" datetime, and going into a datetime datatype, then it really doesn't matter too much about the format because SQL will manage it all for you (so long as it is a recignised datetime data type).  Meaning... Your database might be showing dd/MM/yyyy, but underneath it is a datetime data type

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
I am agree with Mark, you will not be able to convert to datetime from the format you have, none of the first three solution work for you. You can go for Mark's solution but I guess you need dd/mm/yyyy. have a look

declare @dt datetime
set @dt=convert(datetime,replace('21:53:20 Jul 09, 2009 PDT','PDT',''))
select @dt --result will be 2009-07-09 21:53:20.000
select CONVERT(varchar(10),@dt,103) --I guess you want this.09/07/2009

Open in new window

I'm still wondering where that date format is coming from. Presumably no user is providing it in that format (complete with timezone info). Is it a User Control (in which case, the user control should have a method of providing it in a .Net DateTime type), is it another application (same applies), or something else?

The biggest problem with dates is showing them. I'm betting you don't actually have to deal with it as a string at all, and that whatever is formatting it in that way will happily give you the date as a date.


you can simply do all operation in one statement like this:

declare @dt varchar(50)
set @dt='21:53:20 Jul 09, 2009 PDT'
select convert(varchar(10),CONVERT(datetime,replace(@dt,'PDT','')),103)

moreover, if you answer Rob's question, you may find some other good way to do your task.
elmbrookAuthor Commented:
thx 4 all the replies

i decide to use a simple method, using
and that is it
it works fine

coz i just want the transaction date
Mark WillsTopic AdvisorCommented:
Thanks elmbrook,

Given the "real" answer, I am not so sure I deserved the points.

It would have helped had you let us know your "datasource". Being code based we might have zeroed in on the "real" problem a bit earlier. But not to worry, so long as you did arrive at a solution...

That will give you both date and time - but if just needing a date, then you could also use : DateTime.Now.Date  which sets time component to zero, ie 00:00:0000

Mark Wills
Oh, and... since .Net is the source of your data, presumably you're using this to put into a SqlCommand.Parameter... in which case, avoid the .ToString(). You should populate it directly as a Date type, letting the system avoid having to re-parse the date at all.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.