Post reply

Date Format

  • 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.

  • August 20, 2008 at 4:31 am

    #860513

    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

  • August 20, 2008 at 4:39 am

    #860516

    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?

  • August 20, 2008 at 4:56 am

    #860523

    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

  • August 20, 2008 at 4:57 am

    #860525

    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

  • August 20, 2008 at 6:02 am

    #860559

    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

  • August 20, 2008 at 7:34 am

    #860615

    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

    ( format varchar(60),

    format_id int )

    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

    from tbl_format_date

    where format = @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)

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

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