How to specify Date format in SQL Server? 2

[whloo]

Hi,

I am trying to find a way to format the datetime format in sql server.
The default format for sql server is "dd/MM/yyyy hh:mm:ss".
But i want it to be appear in "MM/dd/yyyy hh:mm:ss" format.
How can i specify it?
Thanks!

Regards,
weihann.

 

[JamesLean]

SELECT CONVERT(varchar(10), getdate(), 101) + ' ' + CONVERT(varchar(10), getdate(), 108)

Look up CONVERT in BOL for all the styles you can use.

--James

 

[whloo]

thanks for ur reply james.
but i am trying to put the convert formula into my sql server in the "formula" property/.
if i use CONVERT(varchar(10), getdate(), 101), the getdate() will always give me today date which is not what i want.
lets say i have a lot of records in the table, if i use the formula. it will change all of my date to today date

 

[SQLBill]

Replace the GETDATE() with your data.

I.E.

SELECT CONVERT(varchar(10), '28/07/2003 10:05:03', 101) + ' ' + CONVERT(varchar(10), getdate(), 108)

or

SELECT CONVERT(varchar(10), mydatecolumn, 101) + ' ' + CONVERT(varchar(10), getdate(), 108)

or
DECLARE @mydatevariable datetime
SET DATEFORMAT dmy
SET @mydatevariable = '28/07/2003 10:05:03'
SELECT CONVERT(varchar(10), @mydatevariable, 101) + ' ' + CONVERT(varchar(10), getdate(), 108)

-SQLBill

 

[SQLBill]

OOPPPSS, I didn't replace all the GETDATE()'s correctly.

SHould have been:

I.E.

SELECT CONVERT(varchar(10), '28/07/2003 10:05:03', 101) + ' ' + CONVERT(varchar(10), '28/07/2003 10:05:03', 108)

or

SELECT CONVERT(varchar(10), mydatecolumn, 101) + ' ' + CONVERT(varchar(10), mydatecolumn, 108)

or
DECLARE @mydatevariable datetime
SET DATEFORMAT dmy
SET @mydatevariable = '28/07/2003 10:05:03'
SELECT CONVERT(varchar(10), @mydatevariable, 101) + ' ' + CONVERT(varchar(10), @mydatevariable, 108)

-SQLBill

 

[whloo]

so sorry to response so late.
thanks you very much for the response SQLBill.
I try to put the formula into my sql server but it gave me error.
i am quite new this this sql programming.
what is mydatecolumn? i have to replace it with my column name?
if my column name is 'DateCreated', so i have to replace it with SELECT CONVERT(varchar(10), DateCreated, 101) + ' ' + CONVERT(varchar(10), DateCreated, 108)

pardon me if i am too stupid in this thing....
thanks for your help.
i really appreciate it.

 

[whloo]

opps... my mistake.
now it works.
star for both of u

CONVERT(varchar(10), getdate(), 101) + ' ' + CONVERT(varchar(10), getdate(), 108)

will give me "MM/dd/yyyy hh:mm:ss" format.
what if i want "dd/MM/yyyy hh:mm:ss" format?


i try to use CONVERT(varchar(10), getdate(), 103) + ' ' + CONVERT(varchar(10), getdate(), 108) but it gave me error. how come? can anyone please explain?

 

[SQLBill]

When you installed SQL Server, you should also have installed the Books OnLine (refered to as BOL). If you don't know where it is, go to Start>Programs>Microsoft SQL Server>Books OnLine.

Open that up and go to the Index tab. In the search text box, enter CONVERT. Double-click on CONVERT and then double click on the line that has LOCATION as Transact-SQL. Scroll down and there will be a chart with available conversions.

But to answer your question, change the 101 to 103.

-SQLBill

 

[whloo]

hi SQLBill,

thanks for the response, i tried to change 101 to 103 but it always give me error

CONVERT(varchar(10), getdate(), 103) + ' ' + CONVERT(varchar(10), getdate(), 108)

but when it is 101, it won't give me any error.
how come? is it because of the language i select is English (United State)? I noticed the 103 format is for english (british)....
if so, how can i change the language of the sql server?
thanks!

 

[fredp1]

The Select CONVERT(varchar(10), getdate(), 103) + ' ' + CONVERT(varchar(10), getdate(), 108) works fine.

What was the error message?

 

[whloo]

it didn't work for me for some reason.
go to check your sql server language please.
see if yours is English (United State)?
Mine is English (United State).

It just tell me the formula got error whenever i try to use 103. but when i use 101, it is perfectly ok

 

[SQLBill]

The language for my SQL Server system is ENGLISH. I ran this:

Select CONVERT(varchar(10), getdate(), 101) + ' ' + CONVERT(varchar(10), getdate(), 108)

and got:

08/19/2003 11:50:53

Then I changed the 101 to 103 and ran:
Select CONVERT(varchar(10), getdate(), 103) + ' ' + CONVERT(varchar(10), getdate(), 108)

and got:

19/08/2003 11:50:59

So it should work for you. What's the exact error you are getting?

-SQLBill