All Questions
Tagged with sql-date-functions sql
193 questions
0
votes
2
answers
64
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)
...
- 169
0
votes
1
answer
104
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
56
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
45
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
1
answer
65
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
517
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
39
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
254
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
373
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 ...
- 496
0
votes
0
answers
93
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
91
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
25
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 ...
- 71
-1
votes
1
answer
51
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
0
votes
1
answer
142
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
146
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
175
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-...
3
votes
2
answers
284
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
486
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
144
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
69
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
59
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
108
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
3k
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
399
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
89
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
573
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
668
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
70
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
0
votes
1
answer
54
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
239
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
497
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
100
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
855
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,506
0
votes
2
answers
225
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 ...
- 404
-2
votes
1
answer
1k
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
1
vote
1
answer
86
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
524
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
290
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
3
answers
240
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 ...
- 487
0
votes
1
answer
181
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
0
votes
3
answers
843
views
How can i change date format via sql
I have saved date in my database in 12-Oct-2021 12:27:53 pm this format. But I need to change it to 2021-10-12. How can i do it via SQL.
- 39
0
votes
1
answer
448
views
datetime comparison between two columns of two different tables in sql Server
i have 2 tables who includes thes sames colums but the differentes data.
TOOLS DATE
----------- ----------------------
A10MA17 2021-09-25 05:14:16.000
x10UA50 ...
- 35
-1
votes
1
answer
245
views
Convert Date in Microsoft SQL Server
I have a problem converting a SQL Column (date_column) to 104 date format. In the date column are two different types of dates. One is in dd-mm-yyyy format and the other one is a
consecutive number ...
0
votes
2
answers
3k
views
SQL, casting a string to date so I can use GETDATE()
I am using SQL Server Management Studio 18 against SQL Server 2016. I have some material that are in batches and those batches have expiration dates that are held as strings, but are basically in the ...
- 9
-1
votes
1
answer
2k
views
Can we use arithmetic operators (+, -. *, or /) on date functions in mysql?
I am learning mysql and I am stuck in the following question.
Can we use arithmetic operators (+, -. *, or /) on date functions in mysql?
Can someone help me out with it?
- 277
0
votes
2
answers
286
views
Snowflake Split bigger time interval in monthly intervals
I have a table which has different interval invoice. Please see sample data below:
Invoice_Start_Date Invoice_End_Date Amount
1/1/2019 2/1/2019 12
1/1/2019 1/1/2020 ...
- 404
1
vote
3
answers
552
views
Adding a day in Oracle but losing hour and minute, and format is also changing
I have a table tab1 in which a column col1 has data type VARCHAR2(50 BYTE) and this column has values like '9/27/21 18:05'
I want to add 1 day to this and I am expecting a result like '9/28/21 18:05'
...
1
vote
2
answers
1k
views
(SQL BigQuery) Using Lag but data contains missing months
I have the following table with monthly data. But we do not have the third month.
DATE
FREQUENCY
2021-01-01
6000
2021-02-01
4533
2021-04-01
7742
2021-05-01
1547
2021-06-01
9857
I want to get ...
- 35
0
votes
1
answer
237
views
Query to find the last 4 Tuesday from current date
i'm working on bi report.
So i have a table with
occupancy ID | rent amount | Date of payment
and the rent is paid on every Tuesday of the week. i need a SQL query to find the last 4 Tuesday from the ...
-1
votes
1
answer
101
views
How can i change column with dd--MON-yyyy format to dd-mm-yyyy
I have column name PERIODE with dd-MON-yyyy format and I want to change it to dd-mm-yyyy format, already tried some function but the format didn't changed
- 1