how to change the date format - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how to change the date format

Author

Topic

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-08-04 : 06:07:36

Hello friends

I have 1 lac records in a table.In that one column is referring to datetime.The format of datetime is by default 'mm/dd/yy'. Now i want to change the dateformat to dd/mm/yyyy. It should show all rows containing date in dd/mm/yyyy format.

How can i do this.

Thanks

Zakeer Sk

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-04 : 06:19:58

Where do you want to show converted dates?

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-04 : 06:20:12

if the data type of the column is datetime, then there is nothing to change. Date & time is not stored in table in any particular format but in its internal format.

When you are displaying the datetime from the table, format it to the required format in your application

KH
[spoiler]Time is always against us[/spoiler]

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-08-04 : 07:23:20

i want to update all the dates in the table into dd/mm/yyyy format.

Thanks

Zakeer Sk

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-04 : 07:27:10

hmmm, let's start with the basic questions then... what datatype is your column?

Em

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-08-04 : 07:28:54

datetime data type...... its in mm/dd/yyyy format.how can i change that.

Thanks

Zakeer Sk

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-04 : 07:33:25

then try and understand what Khtan explained earlier. datetime data is not stored in that format. How you see it when you select the data is nothing to do with how it's stored, it's just how you present it

Em

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-04 : 08:32:06

quote:
Originally posted by shaik.zakeer

datetime data type...... its in mm/dd/yyyy format.how can i change that.

Thanks

Zakeer Sk

Did you read my question?

Madhivanan

Failing to plan is Planning to fail

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-08-04 : 08:39:04

Hi Madhivanan

i have table like this

id date(mm/dd/yyyy)
--------------------------
1 08/01/2008
2 08/02/2008
3 08/03/2008
4 08/04/2008

i need to update the same table like this

1 01/08/2008
2 02/08/2008
3 03/08/2008
4 04/08/2008

how can i do this.how can i change the system default dateformat.

Thanks

Zakeer Sk

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-04 : 08:43:42

quote:
Originally posted by shaik.zakeer

Hi Madhivanan

i have table like this

id date(mm/dd/yyyy)
--------------------------
1 08/01/2008
2 08/02/2008
3 08/03/2008
4 08/04/2008

i need to update the same table like this

1 01/08/2008
2 02/08/2008
3 03/08/2008
4 04/08/2008

how can i do this.how can i change the system default dateformat.

Thanks

Zakeer Sk

You dont need to change the date settings. When you want to show them in the front end application, use format function there

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-04 : 08:46:07

quote:
Originally posted by shaik.zakeer

Hi Madhivanan

i have table like this

id date(mm/dd/yyyy)
--------------------------
1 08/01/2008
2 08/02/2008
3 08/03/2008
4 08/04/2008

i need to update the same table like this

1 01/08/2008
2 02/08/2008
3 03/08/2008
4 04/08/2008

how can i do this.how can i change the system default dateformat.

Thanks

Zakeer Sk

There is really nothing you need to do. The datetime is not stored in DD/MM/YYYY or MM/DD/YYYY or what-so-ever format.

see http://msdn.microsoft.com/en-us/library/ms187819.aspx

quote:

Values with the datetime data type are stored internally by the SQL Server 2005 Database Engine as two 4-byte integers. The first 4 bytes store the number of days before or after the base date: January 1, 1900. The base date is the system reference date. The other 4 bytes store the time of day represented as the number of 1/300-second units after midnight.

KH
[spoiler]Time is always against us[/spoiler]

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2008-08-04 : 08:49:43

thanks ..........

Thanks

Zakeer Sk

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-04 : 08:54:00

finally

KH
[spoiler]Time is always against us[/spoiler]

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-08-04 : 20:34:09

I am using Reporting Services and in the layout on the respective field containing datetime datatype I use the following:
This formats date and time seperately. Change the values from 1 to 4 to change format.
=FormatDateTime(Now(),1)+ " " + FormatDateTime(Now(),3)

=FormatDateTime(First(Fields!StartDateRange.Value, "TotalSepDuration"),1)+ " " + FormatDateTime(First(Fields!StartDateRange.Value, "TotalSepDuration"),3)

liza1
Starting Member

4 Posts

Posted - 2008-08-05 : 01:00:08

quote:
Originally posted by shaik.zakeer

Hello friends

I have 1 lac records in a table.In that one column is referring to datetime.The format of datetime is by default 'mm/dd/yy'. Now i want to change the dateformat to dd/mm/yyyy. It should show all rows containing date in dd/mm/yyyy format.

How can i do this.

Thanks

Zakeer Sk

liza1
Starting Member

4 Posts

Posted - 2008-08-05 : 01:06:41

you can create your own date function ,use to_date functions in your Query this will help you
to know more about Sql Database,There are number of good videos tutorial of SQL on http://www.codervods.com/

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-05 : 02:28:27

quote:
Originally posted by harlingtonthewizard

I am using Reporting Services and in the layout on the respective field containing datetime datatype I use the following:
This formats date and time seperately. Change the values from 1 to 4 to change format.
=FormatDateTime(Now(),1)+ " " + FormatDateTime(Now(),3)

=FormatDateTime(First(Fields!StartDateRange.Value, "TotalSepDuration"),1)+ " " + FormatDateTime(First(Fields!StartDateRange.Value, "TotalSepDuration"),3)

what's the format in which you want dates? there are a couple of formats available within format tab itself.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources