Thread: Date Conversion

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2001
    Posts
    124
    Rep Power
    18

    Date Conversion


    I am using the following to retrieve the month name -
    Code:
    SELECT DATENAME(MONTH, GETDATE()) as DefaultValue
    but I would like to modify the GETDATE to be 14 hours ahead (because my location is in a different timezone to my server). I only need the month name to be returned (and it is used as a default in one of my forms) but because of the time difference my location will advance into the next month 14 hours ahead of my server.
    Can it be written in one SELECT?
    Thanks.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Code:
    SELECT DATENAME(MONTH,DATEADD(HOUR,14,GETDATE())) as DefaultValue
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2001
    Posts
    124
    Rep Power
    18
    Thanks for your reply, I'll give it a try.
    Also, is there a way to append some text to the end of the SQL result?
    I would like to add the word "Files" to all results (e.g. AugustFiles").
    Thank you.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    apppend it with the concatenation operator, +
    Code:
    SELECT DATENAME(...) + 'Files' AS DefaultValue
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2001
    Posts
    124
    Rep Power
    18
    Thanks again for your reply, I appreciate your help.
    I have expanded on my earlier code to include a value pulled from my database (instead of the word "Files") and would appreciate it if you could tell me if this is the best way to pull one single value and add it to my DefaultValue.

    Code:
    DECLARE @test nvarchar(max);
    SET @test = (SELECT ItemName FROM Users WHERE UserID=$(UserID))
    SELECT DATENAME(MONTH,DATEADD(HOUR,14,GETDATE())) + @test as DefaultValue
    Thanks.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    you can simplify that, and also do away with the variable --
    Code:
    SELECT DATENAME(MONTH,DATEADD(HOUR,14,GETDATE())) 
           + ItemName AS DefaultValue
      FROM Users 
     WHERE UserID = $(UserID)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2001
    Posts
    124
    Rep Power
    18
    Works fine!!!
    Thank you.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2001
    Posts
    124
    Rep Power
    18
    Thanks for your help.
    I have a more complicated version that I would appreciate your help on.

    We have our users birthday stored in three columns (year, month, day) in the Users table.
    We have a level_adjustment column that stores values from -2 to +2 (including 0) in the Users table. This is for us to manually override a students age based level and almost all values are 0.

    Our School years run from April 1st to March 31st and I want to calculate how old the student was on the previous April 1st from the current time (so based on todays date that means April 1st 2013. If today was March 31st 2014 the calculation still needs to be done on April 1st 2013).
    I then need to assign a level from 0 to 18 based on how old the student was on April 1st. If they were 0 then the level is 0, if they were 1 then the level is 1, etc. If they were 18 OR over then the level is 18.
    Once the level is calculated I want to add/subtract the level_adjustment value and that will give us the final_level_value (if it is less than 0, then we will use 0 instead of a negative number).
    Finally we need to return a result of the current_month_name (based on the 14 hour time difference) + - + final_level_number (e.g. September-10) as the DefaultValue.

    Im not sure how to go about the calculation in the following -

    Code:
    SELECT DATENAME(MONTH,DATEADD(HOUR,14,GETDATE())) 
     + '-' + (calculations here that give us the final_level_number) AS DefaultValue
      FROM Users 
     WHERE UserID = $(UserID)
    This looks like a complicated calculation to me but Im sure it is easy for someone who understands SQL.

    Thank you.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2001
    Posts
    124
    Rep Power
    18
    My apologies, the date is stored in ONE field named birthday (not three fields - year, month, day). It is stored in mm/dd/yyyy format (including the slash marks so - 09/28/1965).
    Thanks
  18. #10
  19. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by uniopp
    This looks like a complicated calculation to me but Im sure it is easy for someone who understands SQL.
    no, it's complicated for us as well

    isn't there anyone you work with who can help you?

    if not, perhaps you could ask your manager to authorize hiring someone on a contract basis
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo