All Questions
Tagged with sql-date-functions sqldatetime
39 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
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
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
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
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
-3
votes
1
answer
363
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
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
717
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,335
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
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 ...
- 404
0
votes
1
answer
37
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. ...
- 404
5
votes
4
answers
10k
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
1
vote
1
answer
51
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 ...
- 35
1
vote
1
answer
439
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
...
- 35
-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
361
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
0
votes
1
answer
167
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
...
0
votes
1
answer
385
views
DATEDIFF causing The conversion of a date data type to a datetime data type resulted in an out-of-range value
Why would the following SQL cause the error?
SqlException: The conversion of a date data type to a datetime data type resulted in an out-of-range value.
I believe it has to do with the DATEDIFF ...
- 24.3k
0
votes
1
answer
172
views
Modifying a query to work without requiring NLS_DATE_FORMAT to be set
I the following query:
SELECT
trunc(estimatedenddate,'hh') AS reg_date,
COUNT(*)
FROM
(
SELECT
attr_value,
TO_DATE( (DATE '1970-01-01' + (1 / 24 / 60 / 60 /...
- 4,360
0
votes
0
answers
162
views
Delete from Mysql table where datetime is older than datetime of another item
I have some difficulty to get my MySQL query right. The current status is like this:
I have a table containing Todo's with a datetime field changed_at (set to current datetime on insert/update)
I ...
- 282
0
votes
2
answers
40
views
Derive a new column based on Time stamp
I have a column 'customer_date' with datatype text.
Based on the time i need to derive a new column
Condition :
when customer_date <12 then 'AM' else 'PM
customer_date
01Mar2018 10:03:54
...
- 839
1
vote
1
answer
14k
views
SAS PROC SQL - How to add months to a date
How can I add months to a date?
I've tried the function ADD_MONTHS(<my date>, 6) and DATEADD(Month, 6, <my date>) but both functions are not recognized.
What's the correct function to use?...
- 1,349
1
vote
0
answers
109
views
Insert into table by filtering date from another table?
As a overview we are trying to analyse one kind of eCommerce delivery business, where our today's business depends on last evening and today's morning orders
And our business team defined the below ...
- 243
1
vote
3
answers
10k
views
How to query last Saturday and the one before in SQL?
I would like to query data, which is between some week days, as follows:
Last Saturday
The Sunday before it
The Saturday before that queried Sunday on 2.
The Sunday before that queried Saturday on ...
- 1,990
1
vote
1
answer
401
views
how to convert GETDATE() in a SQL database for a specific format while creating table?
How can i create a convert function while creating a table,
my following sql script for creating table is.
CREATE TABLE database.dbo.Machine
(HistoryID bigint IDENTITY(1,1) PRIMARY KEY,
...
- 369
0
votes
0
answers
44
views
convert many date formats to its assigned formatted date
I want to convert Non-Standard Date Formats to Standard Date format. Table has Column_A and Column_B I have column_A as input and I want the corresponding value on column_B.
- 1
0
votes
1
answer
663
views
How to get Last Month interval with MSSQL in January
How can this code be fixed, so it doesn't break in January? I believe this part is the bug:
(Month(Getdate()) - 1
SQL:
AND UOnline.maxstamp > dbo.Udf_converttotimeinterval(
...
- 9,650
0
votes
2
answers
270
views
Date won't change after using DATEADD() function in SQL Server 2017
I have a date stored in a string (@cnp=1051597991234) the date being 051597 representing 5/15/1997. I am using the following code to set a date variable with the needed value, in the end to compute ...
- 71
0
votes
1
answer
331
views
How to convert OLE Automation Date to My sql date
How to convert OLE Automation Date to a Mysql standard date type?
- 133
2
votes
3
answers
3k
views
Convert varchar time (for time > 24 hours) to float
I have a column with time records that represent the difference between two dates. Some of these records exceed 24 hours.
As time syntax doesn't account for time records > 24 hours, I've had to ...
- 41
1
vote
3
answers
1k
views
Finding sql date with account year and Quarter
I am new in sql i am facing one problem
I need to create stored procedure which return start and end date of any quarter by passing Quarters as string string and year
ex : when i pass exec spname '...
user5746445
3
votes
4
answers
107
views
SQl Difference in date
I need find difference in two date in years
but years should be in decimal
example
i need to find difference between 16-jun-2010 to 30-Sep-2014
and it should be 4.30 yrs
i try following way
select ...
user5746445
0
votes
2
answers
171
views
Java date to sql date
How can I store a specific date from java to my database? (Not only the date today, but also some dates that the user wants to specifiy)
try {
SimpleDateFormat dateFormat = new ...
- 19
2
votes
1
answer
9k
views
How to use GETDATE() in Oracle to find number of days between Current Date and some specified date?
Suppose I have a table called projects and it has columns as projectid, startdate and enddate. The startdate of each project is specified already and the enddate is specified only for projects that ...
- 465
1
vote
1
answer
996
views
Grouping DATETIME by DATE in MySQL and showing the last record of the day
I have a group of currency quotations that change every hour or so. For the sql results in this particular example, I only need the last available record of each day of the last 30 days in yyyy-mm-dd ...
- 11k
1
vote
2
answers
925
views
Select distinct where date is max
This feels really stupid to ask, but i can't do this selection in SQL Server Compact (CE)
If i have two tables like this:
Statuses Users
id | status | thedate id | name
...
- 2,177
7
votes
1
answer
161k
views
Filter data based on date in sql
Im trying to execute the following SQL query and filter out the data based on the date.
I need to display a table which filters out the data such that, only those rows which are between the mentioned ...
- 3,252
1
vote
2
answers
797
views
SQL date conversion for 8 char value with 00 as days
I have been having trouble trying to figure out how to properly convert the data. In its current form it is varchar and the data is as follows:
19260200,
19770900,
20030400,
20120300,
20020500,
So ...
- 460
0
votes
3
answers
1k
views
Get Day, Month, Year, Lifetime total records with one query w/ optimizations
I have a Postgres DB running 7.4 (Yeah we're in the midst of upgrading)
I have four separate queries to get the Daily, Monthly, Yearly and Lifetime record counts
SELECT COUNT(field)
FROM database
...
- 85.2k