Tag: datetime

how to get 30 days from Todays

Select from x WHERE yid = rid AND (h.test1 <=todaysdate) AND (h.test1>= 30 days from today) How can I write that line 30 days from today in a query? Instead I put a date or a month from todays date.

Date and Time Formatting in VB.net and SQL Server

I have a table in which data is been logged in ‘yy/MM/dd HH:mm:ss’ format and my regional setting is ‘2016-04-02 14:25:15’ type. I want to get details in a following query but it is not populating any results The query I used is select Date_time, alarm_id, alarm_message from table01 where Date_time between ‘” & DateTimePicker5.Value […]

Convert integer value to DateTime in SQL Server 2012

I have a table called “EventLog” which has the column called nDateTime of type int. This is the table “EventLog” with some values: —————– | nDateTime | —————– | 978307200 | —————– | 978307219 | —————– | 978513562 | —————– | 978516233 | —————– | 978544196 | —————– | 1450379547 | —————– | 1472299563 | […]

GET UTC Date of a past date

My database have date values saved in GMT time zone in int format. I am trying to convert the date to local timezone, but the issue arises when the date is a past date, For instance in my case offset for date Dec 1, 2012 will be -5 and for June 15, 2010 will be […]

SQL: Error when converting varchar to datetime

WITH Valid_dates (ValidDate) AS ( SELECT ’19’ + SUBSTRING(column, 0, 3) + ‘-‘ + SUBSTRING(column, 3, 2) + ‘-‘ + SUBSTRING(column, 5, 2) AS ValidDate FROM table WHERE SUBSTRING(column, 3, 2) <= ’12’ –Max month AND SUBSTRING(column, 3, 2) >= ’01’ –Min month AND ISNULL(SUBSTRING(column, 3, 2), ”) <> ” –Empty string AND SUBSTRING(column, 5, […]

Convert Date Stored as NUMERIC to DATETIME

I am currently working on a query that needs to calculate the difference in days between two different dates. I’ve had issues with our DATE columns before, because they are all being stored as numeric columns which is a complete pain. I tried using CONVERT as I had done in the past to try and […]

SQL Server find overlaping date ranges

I have a table with date ranges (seasons) for each year. The normal is that the end of the one season is the beggining of the next season. In the example below I have in bold the two irregular season setups. In the first the end of season 1 is a day after the beggining […]

TSQL function to find difference between two dates taking weekends and holidays into account

Tomalak posted an excellent response to an existing SO question: SQL DateDiff advanced usage? It almost worked for me but I needed to compute the business hours difference between two dates, excluding weekends, even if less than a week had elapsed. My solution adds a while loop (which is probably a bit naive, open to […]

How do you select all records in a 24-hour time period and summarize the data in SQL?

I have a SQL 2014 table containing hourly records of gas meter consumption. Each record contains the DATETIME date along with the meter identifier and the associated gas consumption for the past hour. The problem I am having is we need to know the total daily consumption beginning with the 10:00 am record thru the […]

Find all slots in a given range

How to find all the date slots in a range.. From Date 01- Jan -2014 00:00:00 A.M To Date 15-Oct-2014 00:00:00 P.M Event Start Date 03-Jan-2014 10:00 A.M occurs every 3 days once mean 03, 06, 09, etc I need to calculate like below using c# 03-Jan-2014 06-Jan-2014 09-Jan-2014 12-Jan-2014 etc,etc How this calculations can […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.