changing mm/dd/yy into dd.mm.yy

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 2000 Forums

Transact-SQL (2000)

changing mm/dd/yy into dd.mm.yy

Author

Topic

dejjan
Yak Posting Veteran

99 Posts

Posted - 2005-03-22 : 04:40:01

Hello,
my regional settings is English (United Kingdoom), so datetime format is mm/dd/yy.
I need to import in database column 'date' in format dd.mm.yy. Is there any built in conversion which could change 3/22/2005 to 22.03.2005

Thanks in advanced,

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-03-22 : 04:58:27

Hi
When you say you need to import the date in format dd.mm.yy, do you mean you are storing the dates in a CHAR field?

Mark

SqlStar
Posting Yak Master

121 Posts

Posted - 2005-03-22 : 05:08:17

Can you try like this "Select convert(varchar(10), getdate(), 104)"

:) While we stop to think, we often miss our opportunity :)

Kristen
Test

22859 Posts

Posted - 2005-03-22 : 07:13:40

If you are converting a date like "010203" (lets assume ddmmyy !!) from text (char / varchar) into a date (datetime) column you can use

SET DATEFORMAT DMY

so:

SET DATEFORMAT DMY
DECLARE @MyDate datetime
SELECT @myDate = '01/02/03'
SELECT [DMY] = @myDate
GO
SET DATEFORMAT MDY
DECLARE @MyDate datetime
SELECT @myDate = '01/02/03'
SELECT [MDY] = @myDate
GO
SET DATEFORMAT YMD
DECLARE @MyDate datetime
SELECT @myDate = '01/02/03'
SELECT [YMD] = @myDate

Kristen

dejjan
Yak Posting Veteran

99 Posts

Posted - 2005-03-22 : 10:08:16

no, I don't want to convert in varchar. I want to have datetime format.
This is my situation:
I have access application. One of data is date, which is getdate(). But, because of regional settings its like 3/22/2005.
Database is on SQL Server, and I use that database, the same data, for different application. But from other application, which existing on some other workstation, which has different regional settings, I get date in format - 22.03.2005.
I want to make conversion in stored procedure where I will change 3/22/2005 in 22.03.2005 before data gets into table.
If I must use convert to varchar and then again convert to datatime, fine with me ... but how to do that.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-03-22 : 10:14:18

save dates in iso standard format yyyymmdd and format them on the client. that's the way to go.
select convert(varchar(50), yourDateColumn 112) as DatetoInsert
from MyTable

Go with the flow & have fun! Else fight the flow

Kristen
Test

22859 Posts

Posted - 2005-03-22 : 16:20:19

"I don't want to convert in varchar."

That wasn't what I demonstrated. I was demonstrating having a date in a varchar format and safely converting it based on whether the date was store as DMY, MDY or even YMD.

If you store a date in a column with a datetime datatype it will be stored exactly as required.

If you retreive a date from a datetime datatype it will display in an approriate format. If this format is not what you require it is not an error.

To format the date in a different format is the responsibility of the presentation layer. You can reformat it in SQL (using CAST or CONVERT), but that is most often the wrong place to be trying to influence the formatting.

Kristen

dejjan
Yak Posting Veteran

99 Posts

Posted - 2005-03-23 : 03:18:22

Kirsten, I didn't replay to your comment ("I don't want to convert in varchar.").
I think that your advice could help me.

Thanks to everybody.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-03-23 : 06:13:50

quote:
Originally posted by dejjan

Hello,
my regional settings is English (United Kingdoom), so datetime format is mm/dd/yy.
I need to import in database column 'date' in format dd.mm.yy. Is there any built in conversion which could change 3/22/2005 to 22.03.2005

Thanks in advanced,

One more method

DECLARE @MyDate datetime
SELECT @myDate = '22/3/05'
DECLARE @day varchar(2)
DECLARE @Month varchar(2)
DECLARE @year varchar(4)
set @day= day(@myDate)
set @month =month(@myDate)
set @year=year(@myDate)

select @day+'.'+right('0'+@month,2)+'.'+@year

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources