The tag has no usage guidance.

learn more… | top users | synonyms

0
votes
1answer
19 views

Get data from previous years dynamically

I have a table with columns id, fname, date_field. date_field's records are: 2011-03-01 2012-03-01 2013-03-01 2014-03-01 2015-03-01 2016-03-01 I want the data for the past 2 years for each record ...
0
votes
1answer
45 views

Pull the latest Employee and Manager Comments Based on Modified date

I have a table Comments which is linked to the Plan table. Each time the user adds a comment on a specific plan, the Comments table is populated. This table stores the historical data. I need to pull ...
2
votes
1answer
70 views

Is it bad practice to use separate columns for parts of date values?

I recently posted these questions in order to try and improve the performance for my queries. DBA Q&A 1 DBA Q&A 2 One of the suggestions was that I should use a DATE() column rather than ...
0
votes
0answers
25 views

Creating a stored procedure that creates a temp table and outputs data from a range of dates

I'm using Workbench 5.7. I don't know if it is the problem, or if I am. I want to create a stored procedure that would allow a report to be run over a range of dates. I have looked at all sorts of ...
3
votes
1answer
91 views

Search between dates where all dates appear

I want to search house_id where all days appear within a range of 2 dates from startDate to finalDate. Tables columns are: id house_id date status house_id + date are unique the closest I could ...
1
vote
3answers
65 views

Retrieving the top row of data

I have very limited SQL knowledge and need to report on the top most current row of data within a table. The table holds Effective Date, Information and a Renewal Date. select DATA_KEY_ID, ...
1
vote
1answer
122 views

Conversion fails when converting date average to date in sql

I have take date average from date and trying to get result of average date but it raising error like: Conversion failed when converting date and/or time from character string. sql query executed as: ...
0
votes
0answers
39 views

date/time field value out of range: “21/1/2016” [duplicate]

My PostgreSQL database version is PostgreSQL 9.3.10 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit In both databases, datestyle is set to"ISO, DMY". ATTEMPT 1 ...
0
votes
2answers
138 views

Selecting Only One Record with Most Recent Date Using Table Join

I have a list of 463 CARRIER_CD. I am attempting to retrieve the most current BILL_DT for each of the 463 CARRIER_CD. I have uploaded the 463 unique CARRIER_CD to a database named edw_sbx_bt the ...
6
votes
5answers
380 views

SQL queries to only show most recent purchase records for individual food items

I'm working with a food purchasing / invoice system in MS Access 2013 and am trying to create an SQL query that will return the most recent purchase price for each individual food item. Here is a ...
2
votes
2answers
318 views

Get count based on 15 minutes of interval

I have a table containing Call records: tbl_calls cl_Id cl_StartDate cl_endDate I am passing two parameters @StartDate and @EndDate to my stored procedure. My requirement is to get the count of ...
3
votes
2answers
61 views

How can I get dates that wrap around new year from non-datetime columns?

I have a table in my MySQL database that stores daily data (an entry for each day of the year), but does not use a DATETIME column for this. This is because the data is the same for every year (it's ...
1
vote
1answer
100 views

How can I select records which are between 2 dates based on a date in a field in PostgresSQL?

I have two tables: 1 with an Unique ID with the first time they visited (in date format) and a historical addresses table with the same Unique ID, a start field (date field), an end field (date ...
10
votes
4answers
1k views

Determine 3rd Friday of each month

I need to determine the dates which are the "3rd Friday of each month" for a date range of "1.1.1996 - 30.8.2014" in SQL Server. I expect I should use a combination of DENSE_RANK() and PARTITION BY() ...
1
vote
0answers
24 views

DATE - Normalization, a multiple ranged query, multiple inserts and multiple update

I have a booking system, airbnb style. TABLES -> prices, house, available_date A house has available days and not available days, but sometimes the days have different prices. A weekend is more ...
0
votes
1answer
94 views

Query running super slow on a specific date [closed]

I have the following SQL Query I'm executing on a MSSQL2008 DB: SELECT O.OrderDate AS [Date], O.OrderID AS [Order No], DC.DiscountCouponCode AS [Discount ...
0
votes
1answer
145 views

Teradata sql syntax from date to quarter

Need a Teradata sql syntax to extract quarter from a date. Given below is the expected output Date: (YYYY-MM-DD) 2015-09-12 2015-05-22 2014-12-15 Output 3Q15 2Q15 4Q14 Query which I have ...
6
votes
1answer
60 views

How to encode a known date but unknown year?

I am using a Postgres database, and have a table with a date-time field. For many records, we know the year but not the date. Sometimes we know the date but not the year. And other times we know the ...
1
vote
1answer
58 views

What is the most compact way to calculate and then Update a maximum value for each day's data?

I have a database that stores records for each bundle of material. A sample is taken from each bundle and sent out for assaying (for lead and sulfur). I receive the assay data and write the assay ...
2
votes
1answer
70 views

Query result in a daily date from a date range selection Postgres 9.3

I need to make a report that will output a result in a daily row result based on the datarange selected. Example: Table: sales ->id ->type ->gross ->created_at ->updated_at For ...
1
vote
2answers
72 views

How do you achieve a physically sorted table file organization?

Do I just declare an index on a column in the schema like ? ALTER TABLE `table` ADD INDEX `index_name` (`colDate`) I want the table records to be physically sorted not just when doing an ORDER BY ...
-1
votes
1answer
776 views

How to get dates between two dates from MySQL table?

I want to get dates between two dates from MySQL table EXAMPLE fromdate(2015-01-01 and so on....), todate(2015-01-10...and so on) are the columns i have to get dates between these two columns. ...
1
vote
2answers
172 views

MySQL - How to make a DATEDIFF in years?

This is my first post (and English is not my mother tongue) so I hope I won't screw this post up. I am new to MySQL but I learned a lot with 100+ youtube tutorials and your numerous posts. I designed ...
1
vote
1answer
29 views

Normalize End Date - Nulls or separate table

I was hoping for some advice on best practice in schema design - as far as End Date values are concerned. In this case it is for a Suppliers table - we are required to store information on the Start ...
0
votes
1answer
38 views

How do I drop the TIME portion from DATEADD?

I am trying to create some test data where each Employee has a random birthdate. I accomplished the randomizing part but it did not return result in the format that I want.. NOTE: the DateOfBirth ...
0
votes
2answers
318 views

How to compare dates in SQL?

In a database table I have made a date attribute, but I have set its type to varchar and not Date. My question is: will I still be able to compare such dates in a SQL Query? Dates in my DB are stored ...
1
vote
2answers
329 views

Select Between Date Range Mysql

I want to select records between 2 dates: select * from orders where orders.created <= '2015-07-31'; select * from orders where orders.created >= '2015-07-01' and orders.created <= ...
1
vote
0answers
47 views

How to get Business days per month between two dates?

I need to get the total Business Days Per MONTH between 2 dates. I would like to incorporate a Holidays table too. For Example, @StartDate = '12/26/2013' @EndDate ='03/19/2014' I need the ...
0
votes
1answer
303 views

How to insert all the dates in a date range into Oracle database?

For instance, The date range is from 26th Jul to 1st Aug, that is 26th Jul, 27th Jul, 28th Jul, 29th Jul, 30th Jul, 31th Jul and 1st Aug How can I construct a function (of course, I need to ...
4
votes
2answers
684 views

How do I find the next saturday in MySQL

I have trigger and a stored procedure in it (so SP runs when trigger runs). I need a function which finds the next saturday to put in SP. So lets say, it is Wednesday (2015-7-22) today. If my ...
0
votes
1answer
39 views

Formatting a DATE type in an Oracle database to only accept MMDD

Is there a way to limit the inputs into a DATE type in an Oracle database to only accept inputs formatted with only the day and month (not including the year)?
0
votes
2answers
105 views

Select MAX DATE from joined Table

I have two tables, PROJECT and BILLINGS, as follows: Project_ID Project_Client ---------- ------------------ P1 ABC Company P2 DEF Company P3 GHI Company Proj_ID ...
3
votes
2answers
1k views

Is there a way to calculate yesterday / today of last month / year in SQL?

Is there a way to calculate today (and yesterday) of last month (and last year) in SQL? Assuming today is 26-May-2015 then how to find? Today of last year i.e. 26-May-2014? Today of last month i.e. ...
1
vote
0answers
984 views

How to display all the dates between multiple two dates in a table?

I want to display all the dates between two dates for a particular record And this is the table : ID Start_Date End_Date 1 2013-01-14 2013-01-18 2 2013-02-01 2013-02-04 and now i want to get all ...
2
votes
2answers
390 views

Repeating rows of date intervals based on another table in Oracle

I have a table of maintenance plans with date intervals between each required maintenance period. Fiddle: http://sqlfiddle.com/#!4/af742 create table maint_sched (maint_id number, start_date date, ...
0
votes
0answers
36 views

Pgsql Function to create multiple child tables inherited from parent tables

I need help with pgsql function to create multiple child tables concatenated with date. Inherited from parent tables. I am using the following queries -- Setting date range SELECT CAST( ...
6
votes
3answers
326 views

Convert date in date list condition to list of date ranges

I want to look for all the records that occur on specific dates. SELECT * FROM table1 WHERE date(column) in ($date1, $date2, ...); However, as many of you, know this kind of comparison doesn't get ...
0
votes
1answer
332 views

Create a default value for session valid_until MySQL

Server version: 5.6.22 Homebrew The table: CREATE TABLE `sessions` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_id` bigint(20) unsigned NOT NULL, `date_created` datetime ...
3
votes
2answers
468 views

Retrieve total available days in date range compared against multiple records

Given the following MySQL database structure for a booking system, how can I retrieve all available days between two user supplied dates? | ID | code | date_arrival | date_departure | 4 | APT01 | ...
4
votes
3answers
10k views

get all dates in the current month

i have a query in MySQL which serves me very well by getting all the records within the current month; SELECT date_field,val FROM MY_TABLE WHERE date_field>=(CURDATE()-INTERVAL 1 MONTH); The ...
-4
votes
1answer
30 views

Get currentmilliseconds in postgres

I'm working on timestamp/datetime in my application. I'm using postgres db. What is the way to get current milliseconds (something like 1412706599000) in postgres?
0
votes
1answer
1k views

SQL CHANGE DATE FORMAT IN BULK

I have a database with several hundred fields but my data structure is wrong. It is currently in uk format as follows: d/m/y 01/01/85 01/01/96 23/12/87 What would be the most efficient way to change ...
0
votes
1answer
136 views

Separating working hours with non-working hours SQL [closed]

Please can someone assist? I need to break up data between working hours and non-working hours (weekends also need to be taken into consideration). I'm not really sure where to begin, other than: ...
1
vote
2answers
915 views

Concatenate date and varchar

I am need to select the date stored in a column transactionDate datatype date and time stored in column transactionTime datatype varchar from a table transactionTable in mysql. My query is like ...
0
votes
2answers
75 views

WHERE clause converting INT to DATETIME

Looking for advice on how to match two differing column types: I have two tables: RT-A and DA-B. Under both tables there is a column named DeliveryDate, but the individual tables use differing ...
1
vote
0answers
167 views

Combining date and date/time ranges in PostgreSQL

In a PostgreSQL table, I'd like to store events which have a range telling when the event is happening. However, some events span whole days (i.e., the user does not care about time), while others ...
1
vote
1answer
107 views

How to insert the gathered dates into another table using INSERT SELECT in MySQL?

insert into rfdate (date) select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select ...
2
votes
1answer
144 views

How to select all Mondays (or any day combo like MTW, WTTh) between two given dates in MySQL?

What I see over the internet is just counting how many days occur whenever you input the day of the week you need. Like between May 1 to May 31, how many mondays are there in it. What I need is to ...
0
votes
1answer
37 views

PostgreSQL Date Check Costraint

How could I add a Check-costraint for Date type in PostgreSQL? For example, CHECK 'DateXYZ>01/02/98' A typecast is needed?
1
vote
2answers
176 views

Need help in incrementing the date in oracle

Error thrown as "Invalid number" while trying to increment the date. Given below is the query executed through sqlplus. Please help me on this. select ''''||to_date(:To_date, 'dd-mon-yyyy')+1||'''' ...