Post reply

Where is your sessions Current DATEFORMAT hidden

  • October 1, 2007 at 10:48 am

    #118734

    I wrote a quick script to set the date format so some data could be imported that was formatted DMY, like '23/10/2007'

    simple, it's just SET DATEFORMAT dmy; at the end, i wanted to put the setting BACK to whatever the current format is...and THAT's where i ran into problems. I can't seem to find it.

    it's not part of the exposed SESSIONPROPERTY variables.

    there is a DEFAULT for the current language, which you can find here:

    select dateformat

    from syslanguages

    where langid =

    (select value from master..sysconfigures

    where comment = 'default language')

    but if you change your current settings with SET DATEFORMAT, that value doesn't change. so on to the next level...find anything that has a DATEFORMAT column:

    select object_name(id),* from syscolumns where name like 'dateformat'

    so it looks like the table syscacheobjects might be what I'm looking for...looked and found the column is an integer, and not the description. and there's no reference to my SPID, so I don't know explicitly which row is MINE.

    set dateformat dmy

    select dateformat,* from syscacheobjects where dateformat<>1

    ok, so i start fiddling with my current settings, and find i can use a CASE statement to determine the values for the indexes.

    select dateformat,

    CASE when dateformat=1 then 'mdy'

    when dateformat=2 then 'dmy'

    when dateformat=3 then 'ymd'

    when dateformat=4 then 'ydm'

    when dateformat=5 then 'myd'

    when dateformat=6 then 'dym'

    END AS DateFormated,*

    from syscacheobjects where dateformat<>1

    so I can find the value, but how can I tie syscacheobjects to my current session??

    all i want to do is SET DATEFORMAT @previousvalues, just to prove it can be done, but that's where i'm a bit stuck.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 1, 2007 at 11:01 am

    #737802

    dbcc useroptions


    * Noel

  • October 1, 2007 at 11:04 am

    #737806

    doh so simple; thanks noeld

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 3 posts - 1 through 2 (of 2 total)

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