We help IT Professionals succeed at work.

convert date format

452 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,
Comment
Watch Question

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
This should help you out.

convert(datetime, ur_datetime_column, 103)

Commented:
Try using this:
CONVERT(VARCHAR(10), Your_Field, 103)
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
>> convert(datetime, ur_datetime_column, 103)

Typed in a hurry and it should be
convert(CHAR(10), ur_datetime_column, 103)
Rob FarleyConsultant
CERTIFIED EXPERT

Commented:
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
Topic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Rob FarleyConsultant
CERTIFIED EXPERT

Commented:
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
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.

Author

Commented:
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
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
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
Rob FarleyConsultant
CERTIFIED EXPERT

Commented:
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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.