?
Solved

convert date format

Posted on 2009-07-09
11
Medium Priority
?
426 Views
Last Modified: 2012-05-07
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,
0
Comment
Question by:elmbrook
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
11 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24820451
This should help you out.

convert(datetime, ur_datetime_column, 103)
0
 
LVL 2

Expert Comment

by:lppperes
ID: 24820465
Try using this:
CONVERT(VARCHAR(10), Your_Field, 103)
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24820587
>> convert(datetime, ur_datetime_column, 103)

Typed in a hurry and it should be
convert(CHAR(10), ur_datetime_column, 103)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 15

Expert Comment

by:rob_farley
ID: 24820687
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.

Rob
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 450 total points
ID: 24820769
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
0
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 150 total points
ID: 24821038
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

0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24821051
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.

Rob
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24821066
elmbrook,

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.
0
 

Author Comment

by:elmbrook
ID: 24837033
thx 4 all the replies

i decide to use a simple method, using datetime.now.tostring()
and that is it
it works fine

coz i just want the transaction date
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24837216
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

Cheers,
Mark Wills
0
 
LVL 15

Expert Comment

by:rob_farley
ID: 24837257
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.

Rob
0

Featured Post

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question