Date Format

Question

Post reply

Date Format

Sudarsan Srinivasan

Ten Centuries

Points: 1140
More actions
August 20, 2008 at 4:11 am

#197210

Hi All,
I have a requirement wherein i need to convert the date parameter to the format provided. However, the format would be like "mm/dd/yyyy" or "yyyymmdd"..... etc.. If we know the format as 101, 102... then we can convert it. but just knowing how the date would like, how can we write a convert statement. This i would like to achieve without any CASE/IF ELSE condition to check for every format.
Thanks.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Sudarsan, it's difficult to tell exactly what you mean. One interpretation of your requirements is as follows:

You have dates in an unknown format. These dates are in table columns.

You want to convert these values into dates of a known format, in a SELECT or UPDATE.

Your use of the PARAMETER keyword (convert the date parameter) suggests that you would like this implemented as a UDF.

Can you please elaborate?

Specifically, what output format are you expecting?

Why can't you use CONVERT?

Why can't you use CASE?

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sudarsan Srinivasan Ten Centuries Points: 1140 More actions · Answer 2

sorry for not being precise the first time.

i have a datetime column which would be required to be displayed in the result in a particular format. Now the format is unknown till the run time. this format needs to be passed a parameter to the function which i would be writing to convert the datetime column to the expected format.

the parameter is passed from the regional setting for the date in the client machine. so, a sample parameter would be "dd.mm.yy".

SELECT CONVERT(varchar, COLUMN, 4) would give me the expected format. But the param i get is "dd.mm.yy" and not "4".

TO achieve this, i can write a case statement where it checks for each and every format and assign a variable the corresponding value (101, 102...).

However, is there any other way to achieve this?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

What client software are you using to display the date to the user? Is the default display of datetime provided by this client (and using regional settings) inappropriate for your circumstances?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Tom257 SSChampion Points: 11543 More actions · Answer 4

I'm not sure exactly what you're looking for but this could help

CONVERT can take a variable, you could pass that as a parameter.

DECLARE @Fmt INT

SET @Fmt = 113

SELECT CONVERT(varchar(25), YourDateTimeColumn, @Fmt) AS ConvertedDate

FROM ... etc

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 5

This would be one of the typical cases where a CLR function may help out.

And chances are it will outperform any tsql solution to do the same check/transform.

First have a look if the .Net platform may help out to perform the check for you.

Keep in mind always to store date/datetime/time data in a datetime column (or date or time in sql2008)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Krish-853432 SSC Veteran Points: 299 More actions · Answer 6

Hi,

You can store the formats with the format id's in a table. Then you can match the input parameter with that table to get the format id and use dynamic query to get the result as you expect.

Example:

create table tbl_format_date

(formatvarchar(60),

format_idint)

insert into tbl_format_date values ('mm/dd/yyyy', 101)

insert into tbl_format_date values ('yy.mm.dd', 102)

insert into tbl_format_date values ('dd/mm/yyyy', 103)

insert into tbl_format_date values ('dd.mm.yy', 104)

insert into tbl_format_date values ('dd-mm-yy', 105)

insert into tbl_format_date values ('dd mon yy', 106)

insert into tbl_format_date values ('Mon dd, yy', 107)

insert into tbl_format_date values ('hh:mi:ss', 108)

begin tran

declare @param varchar(20),

@format varchar(3),

@STR nvarchar(4000)

select @param = 'dd mon yy'

select@format = format_id

fromtbl_format_date

whereformat= @param

if @format is null

begin

raiserror('Given format is not available', 16, 1)

return

end

select @STR = 'select convert(varchar(18), date_column_name, ' + @format + ') from table_name'

exec sp_executesql @STR

rollback tran

Thanks & Regards,
Krish.
(Together We WIN)