Menu
I am running queries on an alarm system signal automation platform database in SQL Server 2012 Management Studio, and I am running into some hiccups.
My queries run just fine, but I am unable to refine my results to the level that I would like.
I am selecting some columns that are formatted as DATETIME, and I simply want to take the value in the column and subtract 4 hours from it (i.e., from GMT to EST) and then output that value into the query results.
All of the documentation I can find regarding DATESUB() or similar commands are showing examples with a specific DATETIME in the syntax, and I don't have anything specific, just 138,000 rows with columns I want to adjust time zones for.
Am I missing something big or will I just need to continue to adjust manually after I being manipulating my query result? Also, in case it makes a difference, I have a read-only access level, and am not interested in altering table data in any way.
519
declare @createTime datetime = '2012-10-06 02:29:37.243'; -- original value, default formatting select @createTime; -- formatted select convert (varchar, @createTime, 100); -- subtract 4 hours, formatted select convert (varchar, dateadd (hour, -4, @createTime), 100); The query above that uses dateadd will always subtract 4 hours.
A. Using subtraction in a SELECT statement The following example calculates the difference in tax rate between the state or province with the highest tax rate and the state or province with the lowest tax rate. Applies to: SQL Server and SQL Database.
Add 30 days to a date SELECT DATEADD(DD,30,@Date) Add 3 hours to a date SELECT DATEADD(HOUR,-3,@Date) Subtract 90 minutes from date SELECT DATEADD(MINUTE,-90,@Date)
I want to minus one hour if hour is not less than 4. Ex: if it contains value '1900-01-01 08:44:00.000' then i want to minus one hour from this value. Then it has to be '1900-01-01 07:44:00.000' if the value is '1900-01-01 03:44:00.000' it has to kept as it is. How to solve this issue. SET @Date1='1900-01-01 09:44:00.000' Just use dateadd function.
Well, for starters, you need to know that you aren't restricted to use functions only on static values, you can use them on columns.
It seems that what you want is simply:
SELECT DATEADD(HOUR,-4,YourColumnWithDateTimes)
FROM dbo.YourTable
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
