298 questions
0
votes
2
answers
168
views
Dynamic Column alias based on a function in BigQuery
I am trying to alias a column based on a DateTime function in BigQuery. Below is my query snippet,
SELECT
SUM(CASE
WHEN CAST(AsOfDate AS DATE) >= DATE_TRUNC(CURRENT_DATE,MONTH)
...
- 167
0
votes
3
answers
61
views
SQL Time Intervals
RegistrationDate
2024-10-31 07:30:00.000
2024-10-31 07:50:00.000
2024-10-31 08:40:00.000
2024-10-31 09:30:00.000
2024-10-31 10:40:00.000
2024-10-31 11:30:00.000
2024-10-31 13:00:00.000
2024-10-31 13:...
- 1
0
votes
1
answer
339
views
SQL query to get prior month in Athena
I want to get last month data (fist day of month through last day of month). My data is in UT (universal time) and need to pull in ET (July). I need to count the number or purchases (by counting the ...
- 1
1
vote
1
answer
60
views
Oracle - inserting date in a format not yielding input date format
I am using below insert statement to enter record in table submissions in which column submission_date of date data type:
insert into submissions
values (to_date('2016-03-01', 'yyyy-mm-dd'), 8494, ...
1
vote
1
answer
59
views
How do I find the date before a value changes in T-SQL?
So I have a following table:
CreatedAt ID NewStatus OldStatus
2023-03-08 13:34:57.0000000 1645 Draft NULL
2023-03-22 19:58:51.0000000 1645 Active Draft
2023-...
- 23
0
votes
2
answers
61
views
How to load last 7 days into a string(list)
I'm trying to create a program that watches and searches for any logs within given dates.
How can I load the last 7 days in DATE format (YYYY-MM-DD) to a string/string list, or via SQL, or Qt/C++.
...
0
votes
1
answer
73
views
Convert Local Time {'09:58:00'} to UTC
I have a requirement to store Local time(10:31:00) to UTC(04:31:09) in the SQL Server.
I have used the below query to get the desired result.
I have combined @VitalReadingDate='2024-01-11' and local ...
- 17
0
votes
0
answers
826
views
Need an SQL query with YEAR and MONTH functions in SAP B1 using Service Layer
I'm having issues trying to apply a query in SAP B1 (HANA) through the service layer, so that it can be read from Power BI or Excel. The original query involves grouping costs by equipment, year, and ...
- 53
0
votes
1
answer
41
views
Is there a way to call GROUP BY as a variable within a calculated field in sql?
i am trying to sort by the year as my calculated field is not correct when trying to graph by date. i think if i get the date to be in chronological order then my problem will be solved but i'm not ...
- 1
0
votes
3
answers
329
views
sql group by end of the week on Sundays
I have 3 columns in my table:
bill_date (daily data), country, revenues
How to group by country and get sum of revenues if I want to group my bill_date by the end of every week that fall on Sundays ?
...
0
votes
2
answers
618
views
First day of the next year with rolling year in Oracle
I always need the 1st of the next year for a query in my Oracle. The date format from today's perspective should therefore look like this: 01-JAN-24
However, this year should adjust automatically as ...
- 506
0
votes
0
answers
145
views
TIMESTAMPADD() is giving me an operand error inside the where clause and I cant figure out why
I am using a version of SQL for Advantage Databases.
In the below code, I am attempting to retrieve the total amount from the past year for each customer. However, the TIMESTAMPADD() WHERE function ...
- 3
1
vote
1
answer
137
views
SQL behaviour when using string literal vs using date literal
Suppose I have this two queries:
SELECT * FROM payments WHERE created < DATE '1967-01-01'
SELECT * FROM payments WHERE created < '1967-01-01'
And created field is a date field.
I would like to ...
- 15
1
vote
0
answers
26
views
Why is there no consistency between how parameters are processed in DATEDIFF and TIMESTAMPDIFF in singlestore?
SELECT TIMESTAMPDIFF(DAY, '2014-12-04', '2014-12-05') as DAYS_DIFF returns 1
,but SELECT DATEDIFF('2014-12-04', '2014-12-05') as DAYS_DIFF returns -1.
Why does TIMESTAMPDIFF do date2-date1 whereas ...
- 91
-1
votes
1
answer
85
views
Between conditions with dates has odd behavior
I have this T-SQL query in SSMS:
DECLARE @TodaysDate datetime
DECLARE @AsAtDate DATETIME
SET @TodaysDate = GETDATE()
PRINT (DATEPART(WEEKDAY, @TodaysDate))
IF DATEPART(WEEKDAY, @TodaysDate) = 2 ...
- 2,822
-1
votes
1
answer
56
views
Why does subtracting NOW with DATE column give nonsense numbers?
Hello everyone,
i have a problem with the subtracting a DATETIME column with todays DATETIME.
One of the columns has the HireDate (Date of hiring a certain employee). I want to know how many Years ...
- 21
-4
votes
1
answer
95
views
Convert date into Weeks in PostgreSQL
I want to add week against dates, start day of the date should be Saturday. can anyone tell me how to do it.
enter image description here
I tried below code but it didn't solve my purpose
select ...
0
votes
1
answer
190
views
Return day from Date in Redshift
Is there a way/function in redshift that returns name of the day from date? For example, if my column is 07/19/2023, it should return Wednesday
- 23
0
votes
1
answer
184
views
Assign the SUM value to the MAX date present for that ID
I have got RequiredQty (A) and ReceivedQty (B) for ID along with dates. I want to sum A and sum B, subtract the difference for each ID and assign this difference to the MAX or LATEST date.
SELECT ...
- 19
0
votes
2
answers
204
views
How to write out/convert YYYY-MM-DD to "Month, DD, YYYY" in SQL
I'm trying to convert some dates I have that written as 2004-01-05 into January 1, 2004.
I can't use DATEFORMAT() because it's not a supported built in function in my database. I'm using the up-to-...
0
votes
1
answer
142
views
SSRS Expression Not Adding 18 Months Correctly to a Date
In one of my reports I have this query expression.
=iif(isdate(Fields!Date_of_Funding_Approval.Value),iif(isdate(Fields!Extension_Expiration_Date.Value),Fields!Extension_Expiration_Date.Value,DateAdd(&...
- 1,305
3
votes
2
answers
318
views
To find Lowest Experience of any Employee by writing a query in GRID DB
I am working on a EmployeeDetails table. I have 4 columns in that table(Empid,Empname, DateOfJoining, Salary). I want to write a query to fetch the Empid,Empname and salary of the employee who is ...
- 51
0
votes
1
answer
71
views
How to change the timestamp format to string and use it for search function
I tried this ,
$date = date('D, M dS Y g:i a', strtotime('2018-03-01 11:54:33'));
echo $date; // will print Thu, Mar 01st 2018 11:54 am
It worked , but I need same in search function ,
$table->...
- 25
0
votes
1
answer
590
views
Snowflake sql query to assign weeks to a month
I know about Snowflake date function to find out day, week, month, year, etc.
I want to have weeks start from Saturday each week to next Saturday.
following gives an idea how to extract, but need ...
0
votes
1
answer
201
views
Function to convert Persian(Solar) Persian(Solar) to Persian(Solar) in oracle
I need to function to convert Persian(Solar) Date to the Gregorian date
A function should be written to receive a solar date in the format (yyyymmdd) and display the equivalent Gregorian date in the ...
-1
votes
2
answers
76
views
How to extend data with respect to incomplete dates in T-SQL?
I have the first table like below:
Node Date Value
01R-123 2023-01-10 09
01R-123 2023-01-09 11
01R-123 2023-01-08 18
01R-123 2023-01-07 87
01R-123 2023-01-06 32
01R-123 2023-01-05 22
...
- 125
-1
votes
1
answer
61
views
Count of Employee only when first_stamp is between a window of start_dt
I want a count of emp_id only when firststamp date is in a 21 day window depending on startdt.
However, I want to count that empid, only when the firststamp either 7 days before the startdt, during ...
- 23
0
votes
1
answer
754
views
Stored procedure to increment date by 1 day for each row
I need a stored procedure that goes through all the records and increases the date by 1 each time. I.e. every record in the table currently has the 13th June 2008. So afterward it should look like ...
0
votes
1
answer
110
views
Window Function - date ranges
I'm trying to calculate duration between different status. Which is working for most part.
I have this table
Table
for id = 102, I was able to calculate duration of each status.
with ab as (
...
- 1
0
votes
1
answer
4k
views
Date Format syntax in where clause in Teradata
I have a table with date column in which date is updated in this format - 11/21/2022.
How can I get the results for the last 15 days using this date column in Teradata? Looks like need to change the ...
0
votes
3
answers
453
views
How to display all columns of the datatype DATE when one column is actually a date and others are just time?
I am learning SQL and using Oracle SQL Developer.
I have a table that contains the following columns
FlightDate
DepartureTime
ArrivalTime
I have inserted values using either
TO_DATE('10:45', 'hh24:...
- 13
0
votes
1
answer
128
views
To display all the months from a given date range
Just wanted your help. I have start date and end date and I want to display all the months between them using SQL.
Can anyone please let me know how can we do this?
If I have start date as 12-09-2022 ...
1
vote
1
answer
669
views
Get dates for last 30 days dynamically in SQL
I have below SQL which gives me count of files received in particular country according to date. But here dates are hard coded. I want them dynamically. I want it in such a way that whenever I run ...
- 49
0
votes
2
answers
831
views
How to get dates as column names in SQL BigQuery?
I would like to get dates (monthYear,weekYear,etc.) as columns, and having the value as count activities by users, but i'm not reaching it :(
Some example:
My table
userid
activityId
activityStatus
...
-1
votes
1
answer
80
views
SQL Case between Date Ranges with HolidayFlag
I'm trying to add days for following up when a holiday falls during one of the scheduled follow up days. In this case July 1 was the charge date and should be followed up on no later than July 5, but ...
- 3
-3
votes
1
answer
400
views
how to convert month date in a text column into datetime
I have a table containing two columns like this:
Month_Date Year
Dec 31 2018
May 01 2020
Jun 05 2021
Jan 18 2022
Jul 19 2019
I hope to combine the Month_date and year in the ...
- 625
0
votes
1
answer
59
views
Is there a way to organize by last invoice date by job?
I am fairly new to SQL and am looking for a solution. I am looking to find the best way to get the newest date from transaction date while being grouped by Job reference. Since these are different ...
- 1
0
votes
1
answer
249
views
Convert string to date type in Athena AWS SQL
I have a column that called date with the following format : '20220816'
how can I convert this column into Date format?
I tried to use this function : date_parse(date,'%y/%m/%d') but it keep giving ...
- 1
0
votes
1
answer
571
views
is there a function to change current date to an assumption date in SQL?
I have a list of data with dates dated as 2021-01-01 to 2021-12-31.
Hence i do need to make an assumption that my current date as 2021-01-01 instead of current date (2022-07-18)
Is there a way that i ...
- 9
-1
votes
2
answers
106
views
CASE WHEN function & date function to change now() > to an assumption date
WITH latest AS (
SELECT
DISTINCT customer_id,
MAX(submitted_on) AS latest_order
FROM orders
GROUP BY 1
),
AA AS (
SELECT
DISTINCT o.customer_id,
latest.latest_order,
now() - INTERVAL '91 ...
- 9
-1
votes
2
answers
1k
views
How to get WeekDay in between two days in SQL server
Please help, I have a below sample data. How to find week days "Tuesday" and count between two days.
CREATE TABLE EmpDetails1 (id INT, name VARCHAR(25),startdate datetime,enddate datetime)
...
- 2,584
0
votes
2
answers
236
views
How to split annual record in 12 monthly records
I have invoice records which are monthly, Annual, Bi-annual etc. I also have a field which provided number of months ( value will be 2 for monthly, 12 for annual etc.)
I need to convert annual invoice ...
- 434
-2
votes
1
answer
2k
views
How to retrieve data for last 2 hrs from a specific Datetime
I am trying to extract last two hours records from a specific datetime. That means, I need to find out if there's any transaction occurred before 2019-11-20 18:00:00. I have 100 records against which ...
- 469
0
votes
1
answer
48
views
Is there a way for me to specify the date function in MySQLWorkBench so that I won’t get an error message?
I tried to create a table, this is how I set it up:
CREATE TABLE emp_tab
(
empno NUMeric(10),
name VARCHAR(50) NOT NULL,
job VARCHAR(50),
manager ...
1
vote
1
answer
93
views
How to sum multiple hour : mins in sql?
i need to sum this varchar values.
time 1 = '13:06'
time 2 = '18:59'
time 3 = '14:49'
i tryed this.
SELECT
convert(
char(8),
dateadd(
second,
SUM(
DATEPART(
...
1
vote
2
answers
589
views
Getting week of the month. Week should start on Monday and end a Sunday
I need to add new business wee to my dim_date actually i have week of the month that is counting every 7 days of month as the table bellow.
Date
Week of month
day_of_week
01/04/2022
1
Friday
02/04/...
-1
votes
1
answer
428
views
How can I join two tables on an ID and a DATE RANGE in SQL
I have 2 query result tables containing records for different assessments. There are RAssessments and NAssessments which make up a complete review.
The aim is to eventually determine which reviews ...
- 43
0
votes
1
answer
36
views
I need a query to create a week circle
I already write this query
Select to_date(concat(to_char(kyc.kyc_verify_date,'YY-MM'),'-01'),'YY-MM-DD') AS "Month"
Count (columnname)
From kyc;
And the table result i given ...
- 71
0
votes
3
answers
253
views
SQL Date Ignore few seconds difference and fetch latest record
We have Service One that creates user details in one place and sent to other Service two with the creation date.The other service uses the same creation date, so in both places it will be the same ...
- 497
0
votes
1
answer
194
views
Before/After Difference for a flexible Date (Case)
i have a little problem with a case. I output two date fields (yyyy-mm-dd). the date changes and is flexible. not every entry has the same date fields.
I take one date field as an fixed point. If ...
- 58