August 31st, 2013, 06:52 AM
I am using the following to retrieve the month name -
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.
SELECT DATENAME(MONTH, GETDATE()) as DefaultValue
Can it be written in one SELECT?
August 31st, 2013, 07:27 AM
SELECT DATENAME(MONTH,DATEADD(HOUR,14,GETDATE())) as DefaultValue
August 31st, 2013, 10:27 AM
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").
August 31st, 2013, 01:03 PM
apppend it with the concatenation operator, +
SELECT DATENAME(...) + 'Files' AS DefaultValue
August 31st, 2013, 09:59 PM
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.
DECLARE @test nvarchar(max);
SET @test = (SELECT ItemName FROM Users WHERE UserID=$(UserID))
SELECT DATENAME(MONTH,DATEADD(HOUR,14,GETDATE())) + @test as DefaultValue
August 31st, 2013, 10:43 PM
you can simplify that, and also do away with the variable --
+ ItemName AS DefaultValue
WHERE UserID = $(UserID)
September 1st, 2013, 02:44 AM
September 1st, 2013, 09:10 PM
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 today’s 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.
I’m not sure how to go about the calculation in the following -
This looks like a complicated calculation to me but I’m sure it is easy for someone who understands SQL.
+ '-' + (calculations here that give us the final_level_number) AS DefaultValue
WHERE UserID = $(UserID)
September 2nd, 2013, 04:55 AM
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).
September 2nd, 2013, 09:42 AM
no, it's complicated for us as well
Originally Posted by uniopp
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