how to format the date data type input in 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)

how to format the date data type input in DD-MM-YY

Author

Topic

verybrightstar
Starting Member

16 Posts

Posted - 2004-03-04 : 01:15:12

hi all , does SQL server able to input the date that is originally from 2/6/2004 11:07:46 AM to DD-MM-YY 06-02-04 and store in the DB. For example , this is the SQL insert query

INSERT INTO ADMIN ( ID , NAME , DATE ) VALUES ( 'A001' , 'karen' ,'2/6/2004 11:07:46 AM')

the date will automatically converted to 06-02-04 and store in DB .

Does it need to use SQL Rule or user-defined data type.

kt

Mathias
Posting Yak Master

119 Posts

Posted - 2004-03-04 : 02:04:15

Yes, you can use this function

ALTER function dbo.usf_FormatDate ( @dDate datetime,
--Date value to be formatted
@sFormat varchar(40))
--Format for date value
returns varchar(40)
as begin
-- Insert the Month --
set @sFormat = replace(@sFormat,'MMMM',datename(month,@dDate))
set @sFormat = replace(@sFormat,'MMM',convert(char(3),datename(month,@dDate)))
set @sFormat = replace(@sFormat,'MM',right(convert(char(4),@dDate,12),2))
set @sFormat = replace(@sFormat,'M1',convert(varchar(2),convert(int,right(convert(char(4),@dDate,12),2))))
-- Insert the Day --
set @sFormat = replace(@sFormat,'DDDD',datename(weekday,@dDate))
set @sFormat = replace(@sFormat,'DDD',convert(char(3),datename(weekday,@dDate)))
set @sFormat = replace(@sFormat,'DD',right(convert(char(6),@dDate,12),2))
set @sFormat = replace(@sFormat,'D1',convert(varchar(2),convert(int,right(convert(char(6),@dDate,12),2))))
-- Insert the Year --
set @sFormat = replace(@sFormat,'YYYY',convert(char(4),@dDate,112))
set @sFormat = replace(@sFormat,'YY',convert(char(2),@dDate,12))
--Insert Hours and minutes
set @sFormat = replace(@sFormat,'HH:NN:SS',convert(char(8),@dDate,108))
set @sFormat = replace(@sFormat,'HH:NN',convert(char(5),@dDate,114))

-- Return the function's value --
return @sFormat
end

Regards, Paul

harshal_in
Aged Yak Warrior

633 Posts

Posted - 2004-03-04 : 02:07:28

quote:
Originally posted by verybrightstar

hi all , does SQL server able to input the date that is originally from 2/6/2004 11:07:46 AM to DD-MM-YY 06-02-04 and store in the DB. For example , this is the SQL insert query

INSERT INTO ADMIN ( ID , NAME , DATE ) VALUES ( 'A001' , 'karen' ,'2/6/2004 11:07:46 AM')

the date will automatically converted to 06-02-04 and store in DB .

Does it need to use SQL Rule or user-defined data type.

kt

INSERT INTO ADMIN ( ID , NAME , DATE ) VALUES ( 'A001' , 'karen' ,convert(varchar,'2/6/2004 11:07:46 AM',105))

He is a fool for five minutes who asks , but who does not ask remains a fool for life!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources