181 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
1
vote
1
answer
632
views
Postgresql is not accepting datetime format from CSV file
I am trying to load a CSV file to my PGadmin database (on my local computer). I am running PostgreSQL16 with PGadmin4 as the user interface. I am trying to upload the date data as follows:
UniqueID
...
- 35
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
votes
2
answers
96
views
Return past 3 appointment and next 3 appointments
I need to get the past 3 appointments and the next 3 appointments from a certain date. Each of these queries give the expected result. My problem is that I need the results together. I can't use UNION ...
- 11
0
votes
1
answer
536
views
SQL Oracle find records that were made until specified time
I'm quite new to SQL and I got stuck on one thing.
Let's say that I have a database, where new records are being created every few mins and there is a 'dstamp' row which contains time when specific ...
- 3
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 ...
1
vote
4
answers
104
views
Split column, and then convert different date types to standard date format (YYYY-MM-DD) - SQL Server
I have requirement to split column in 2 separate columns and then convert multiple date types column to standard date format column.
CREATE TABLE Report
(Id INT, Reference VARCHAR(30));
INSERT INTO ...
- 401
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
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
682
views
Java / SQL -How to group time in intervals of 30 minutes or 1 hour
I need a solution to group the times of entry and the display the respective time interval and count of users/records during that time interval
eg
Time Interval Count
07:00 AM-07:30 24
12:30 PM-...
0
votes
1
answer
62
views
Pass sql date from talend to Rest Api
I want to send a post request to the rest API which includes an SQL date format, when I try to send the request with YYYY-MM-DD format, its showing bad request. How to properly send the data.
- 45
-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
-1
votes
1
answer
5k
views
How to extract day of week from timestamp
I am trying to extract the day of the week from a timestamp in SQL Server.
I am specifically looking for the SQL Server equivalent syntax to EXTRACT.
I want to count how many fields are in each day of ...
- 1
0
votes
1
answer
387
views
Conversion failed Error SQL Date format Mismatch with C# Datatable datetime fromat depending on system calender setting
We have a Stored Procedure in SQL Server which expects datetime column to be in MMddyyyy format and results in error
Conversion failed when converting date and/or time from character string.
...
0
votes
1
answer
416
views
How do I import Dates from Excel to SQL Server varchar column in m/d/yyyy format?
I have an excel from where I am bulk importing data to SQL server through .Net code (Dataset.WriteToServer method).
Now, my excel has dates in m/d/yyyy format, e.g. 9/21/2021. The destination SQL ...
1
vote
1
answer
181
views
Improving group by query for large time-series dataset
I have a large table with time-series data for a whole year, with 24 values for each day (one per hour), for multiple customers.
Customer-ID
Date
Value
001
2020-01-01 00:00:00
xx
001
2020-01-01 00:01:...
- 157
0
votes
1
answer
294
views
cannot insert datetime field with stored procedure into mysql database
I am getting this syntax error when trying to insert a datetime into mysql database.
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds ...
- 123
-1
votes
1
answer
847
views
How to get rows in a table that start from a specific date and time in SQL?
In one of my SQL Server tables, I have te following rows:
Date
Time
2021-10-01
10:00:00.0000000
2021-10-01
11:00:00.0000000
2021-10-01
12:00:00.0000000
2021-10-02
10:00:00.0000000
2021-10-02
11:00:00....
- 2,877
-1
votes
1
answer
136
views
How to get the current month in SQL query?
I run this type of query every month and I have to change the month number manually for the current-running month.
SELECT *
FROM table_1
WHERE year = '[current month]'
Is there any way I can ...
- 11
0
votes
1
answer
478
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
765
views
How to do less than equal to for date in sqlite
When I try to run the below query I get no result even though the date time is same as that in the table.
The BillDateTime column is of TEXT Data type
SELECT * FROM BillingTransaction WHERE ...
- 9,731
0
votes
2
answers
309
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 ...
- 434
-2
votes
4
answers
1k
views
Get Last and Next Appointments
I have the following table in SQL Server and would like to get the last and next appointments for each customer.
Note: If the first appointment is in the future, last appointment should be N/A. ...
1
vote
3
answers
2k
views
SQL query for CASE WHEN 2 dates match
New to SQL so sorry if this is a basic question.
Looking to create a column that indicates a match in two dates (i.e. the date the record is created is identical to the date the record is expired).
My ...
- 449
0
votes
1
answer
1k
views
Conversion of date data type to datetime resulted to invalid date
I am trying to covert date value to datetime in SQL with the following query,which is resulting to invalid value:
select convert(datetime,date_1, 25) from table1;
date_1(of data type date): 2019-12-02
...
2
votes
2
answers
3k
views
Join on closest date in SQL
Hi I have two tables which I have to join on ID field and then on closest date ( only if the date is within one hour range more or less), the reason is both tables are not updated at same time so time ...
- 434
0
votes
1
answer
38
views
Find minimum overlap of Each Status
I need to find date ranges where status is Missing/Not Ready in all the groups ( Only the overlapping date ranges where all the groups have status of missing/notready)
'''
ID. Group. Eff_Date. ...
- 434
5
votes
4
answers
12k
views
Get the max possible time of a date SQL Server
What I am trying to do is take a date in SQL Server and find the last possible hour, minute, second, and millisecond of that date.
So if the date is this: 2021-02-16 13:08:58.620
I would like to ...
- 258
0
votes
2
answers
2k
views
How to convert CST to UTC in MSSQL
Is it possible to convert CST to UTC in MSSQL?
For example:
convert Thu May 13 11:14:19 CST 2021 to 2021-05-13 11:14:19.000
- 3
5
votes
3
answers
26k
views
How to add hours, minutes and seconds to a datetime column in SQL?
I have the following datetime column in my table "UpdatedTime"
Sample value: 2021-12-31 00:00:00.000
I need to manipulate the hours, minutes and seconds to become 23:59:59 (i.e. a second ...
- 65
0
votes
1
answer
316
views
Convert a column of type varchar(255) to type date in PostgreSQL
I'm a new in SQL and I'm trying to convert a column of type varchar(255) to datetime (yyyy-mm-dd). I found some advice to convert one element but how can I convert entire column to a new datatype?
My ...
- 21
0
votes
2
answers
488
views
DbFunctions or similar to compare time of day of a datetime type column in Linq to Entity Framework
I know there is a function to compare just the date of a datetime type column, but what if I want to only compare the time of it to another time. Is there a function in Linq To Entities?
DbFunctions....
- 23.4k
1
vote
1
answer
57
views
Is there a way to acess previous specific value in SQL?
I am working in Oracle live SQL with this same table with data for the whole year (in which the intervals can be larger).
This is just an example(part of the whole table):
I have to add two columns ...
- 33
1
vote
1
answer
472
views
Creating date table from dual
I have been trying to figure out how to create a table out of this code, but can not figure it out. Thank You so much for any answers.
WITH
dat_param AS (SELECT SYSDATE AS dat FROM dual)
SELECT
...
- 33
0
votes
3
answers
90
views
Calculate start date of almost equal periods
SQL Server
CREATE TABLE [TABLE_1]
(
PLAN_NR decimal(28,6) NULL,
START_DATE datetime NULL,
MAX_PERIODS decimal(28,6) NULL,
);
INSERT INTO TABLE_1 (PLAN_NR, START_DATE, MAX_PERIODS)
...
- 585
1
vote
1
answer
148
views
How to fetch records based on from and end time in mysql
I am facing one problem for fetching records from table. My table look likes below:-
id market_id opening_time closing_time day p_slot
1 1 06:30:00 ...
- 135
0
votes
0
answers
175
views
How can i input the correct Timestamp corresponding to the time, when the data was retrieved in sqlite3?
I am modifying a code snippet from a udemy course, to fit my goal. My goal - > being able to store the timestamps in a sqlite3 database corresponding to the actual time the data was retrieved.
The ...
- 1
1
vote
1
answer
697
views
Postgres: global setting to always return TIMESTAMPTZ in exactly the same format? i.e.: 6 digits of sub-seconds + 4 digits of timezone offset
Postgres will return TIMESTAMPTZ values using inconsistent formats, depending on how many zeros are after the .
See these examples:
2020-10-24 13:21:05+10
2020-10-24 13:21:05.1+10
2020-10-24 13:21:05....
- 2,719
-2
votes
2
answers
2k
views
Split date range into new records in Snowflake View [duplicate]
I have a table that consists of start and end dates and I need to need split records into day-wise and this needs to go in a View.
| PersonID | CompanyID | Start_DT | End_DT |
|-----------|--...
- 185
0
votes
1
answer
375
views
Problem with finding datetime entries which are related to today in a SQL Server table
I have a table with a varchar column with different values in it. Some of the values in this column are in fact datetime stamp but written in varchar data type.
I wish to find entries which contain ...
- 389
2
votes
1
answer
89
views
Formatting date to 2017-06-21T12:33:47.879 format
In DB I have TIMESTAMP datatype like 17/06/21 12:33:47,879000000 and I would like to select in format 2017-06-21T12:33:47.879
Edit:
I tried to_char(MODIFIED_TIME,'YYYY-MM-DD HH:MI:SS:FF') but getting
...
- 85
0
votes
1
answer
186
views
Showing a row for every minute based on start and end time
I have a table that looks like below:
task_id start_date end_date
t1 2020-05-01 8:00:00 2020-05-01 9:45:00
t2 2020-05-01 8:30:00 2020-05-01 9:00:00
...
1
vote
2
answers
2k
views
How to delete records from database table older than 30 days with based on date column
This is the code in python that i am using below. But its not working and not showing any error.
delquery = "DELETE FROM table1 WHERE date_column < date ('now', '-30 day')"
conn.execute(...
- 451
0
votes
1
answer
2k
views
SQL Server - Convert datetime to JSON date format
How can I convert SQL smalldatetime or datetime like this:
select cast(getdate() as smalldatetime)
To JSON format like this:
/Date(1576278000000+0100)/
- 3,907
0
votes
2
answers
943
views
Select query to return only the min datetime without group by?
My table displays as below,
|_______________|____________|_____________________|______________________|
| ENTRY POINT | NAME | DATE OF ENTRY | DATETIME OF ENTRY |
|_______________|...
0
votes
1
answer
383
views
Will a SQL view filtered by large datetime always provide current data?
If I create a view today for a table that continuously keeps getting data, using date filtering from 1900 and 2100, for example, will that "copy" become truncated to the moment that I build the view ...
- 446
0
votes
2
answers
1k
views
Select a date in a string and convert it to datetime
I have a string like:
'SPY US 03/20/20 P45'
I want to select just the date from the string.
My current query is:
Select Ticker, SUBSTRING(Ticker, PATINDEX('%[0-9][0-9]/[0-9][0-9]/[0-9][0-9]%',o....
- 59
1
vote
1
answer
44
views
MySQL get dates from a range for which the records does not exist in the database table
I've a database table as below:
Sales Table
id product_id for_date
1 10 2019-01-03
2 12 2019-01-05
3 16 2019-01-10
I want to get dates from between a custom date ...
0
votes
2
answers
91
views
SQL BETWEEN Time doesn't show results
Could use fresh POV help :)
Need to show trips between midnight and 4AM. StartTime column is of type DATETIME. I already tried
CAST(StartTime AS TIME) BETWEEN CAST('00:00:00' AS TIME) AND....
but ...
- 27
1
vote
1
answer
242
views
Pyspark sql add letter in datetype value
I have epoch time values in Spark dataframe like 1569872588019 and I'm using pyspark sql in jupyter notebook.
I'm using the from_unixtime method to convert it to date.
Here is my code:
SELECT ...
