convert date format

This should help you out.

convert(datetime, ur_datetime_column, 103)

Try using this:
CONVERT(VARCHAR(10), Your_Field, 103)

>> convert(datetime, ur_datetime_column, 103)

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

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

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.

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

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

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