The tag has no wiki summary.

learn more… | top users | synonyms

0
votes
1answer
22 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
454 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
28 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
57 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 ...
1
vote
0answers
64 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 ...
1
vote
2answers
53 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
31 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
160 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
49 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
194 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 | ...
2
votes
3answers
938 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 ...
-2
votes
1answer
23 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
155 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
3answers
143 views

MySQL Date field: filter by LIKE vs MONTH()

I have a CRON task that needs to extract customers with birthday in a given month, from a MySQL INNODB table. The birthday field is indexed and of type DATE. I can query the customers table by either ...
0
votes
1answer
73 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
333 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
57 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
91 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
72 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
63 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
33 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?
0
votes
1answer
61 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||'''' ...
2
votes
1answer
183 views

How to model schedule and keep good constraints?

I have some data that I am having a hard time modeling. Particularly, I am unsure of how I can ensure that I have unique data. This is for a schedule where I am assigning teams to stores. A team will ...
1
vote
0answers
57 views

Migration error: “Invalid date literal detected” when reading decimal

I'm attempting a migration from MSSQL to MySQL and keep coming across a "Invalid date literal detected" error when reading certain decimal columns. I'm running the following (all up-to-date as of ...
0
votes
1answer
77 views

Retrieving month from date field in my database

i have dates stored in database with the function : CURDATE() , when a user makes an order . Now with a form where the admin chooses the month and the year , i want a query to display all the orders ...
1
vote
1answer
278 views

2 Date Ranges - How many days from one range excluding weekends overlap

First - Thanks for your help. I'm looking to create a query in SQL that provides the me the # of days from one date range that overlap another date range and excludes weekends. Example Data: ...
3
votes
2answers
563 views

Which of the following pieces of SQL is most efficient

I need to add the date part of GetDate() to the time part of a stored DateTime. Naively I thought I could do the following: declare @testTime DateTime = '2013-04-23 13:55:06' select cast(getdate() ...
0
votes
1answer
185 views

SQLCMD - add date to CSV. When run inside Command Prompt it works, when in Job then %date

I've been battling for hours to add a date to a CSV name: For brevity, Im just taking the first character of the date here. In SQL Command, this works: sqlcmd -S localhost -d master -E -W -w 999 -s ...
-1
votes
1answer
229 views

How to select different format of the date in mysql

0 down vote favorite i have this table and i cant change its format to yyyy-mm-dd because i have so many scripts that are related to this format dd-mm-yyyy , it would be a mess if iam gonna change ...
2
votes
1answer
303 views

How to find number of days since beginning of fiscal month?

This relates to my question How to find XXth day of previous month in SQL server? The fiscal month starts on 26th of each month. I need to find out from any date: The date of the start of the ...
1
vote
1answer
104 views

Date format on the output of cmdshell dir C:\ /TC

I would like to ask you guys if anyone of you had this trouble before. In SQL I read the create time of the file like exec master.dbo.xp_cmdshell 'dir C:\ /TC' which gives me; 07/14/2009 04:20 ...
0
votes
1answer
524 views

Whether to use or not to use: DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

In one Database table, created_at & updated_at are defined as: `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP `updated_at` timestamp NOT NULL DEFAULT ...
0
votes
0answers
19 views

Date Range Limit SQL [duplicate]

I am trying to create a report that calculates how many contracts have been funded each month for the year 2014. The problem is I am trying to limit the dates, meaning I want the user to be able to ...
3
votes
2answers
695 views

Datetime column vs Date + Time columns

I am designing a table for a system that saves a number of logs. We are looking at ~200 entries per second. We are using SQL-Server 2012 Enterprise Edition. I have a question regarding breaking up a ...
0
votes
1answer
2k views

selecting records that are 3 days from expiring datetime

I have a table that contains a datetime timestamp. What I am looking for is to select records that are exactly 3 days from the 30 day expiration based on the datetime timestamp. Here is what I have ...
0
votes
1answer
302 views

how to update date field in a record to null?

I have records in a table which a field named as 'validity' contains date as '8/15/1947 12:00:00 AM'. I need to set this date as null. I am having 1000 records,in that validity field is '8/15/1947 ...
4
votes
1answer
333 views

date formatting in Oracle

this query: select to_char(sysdate, 'Ddspth MONTH, Yyyysp') from dual will return: Eighteenth MARCH , Two Thousand Fourteen i am wondering if there are any additional formatting keywords that ...
0
votes
2answers
113 views

compare dates in results

I'm looking to compare the dates in the result set Example: SELECT * FROM tbl WHERE foo=bar Output id, datetime, name 1, 2014-01-01 18:13:45, test 1 2, 2014-01-01 19:01:32, test 2 3, 2014-01-01 ...
1
vote
2answers
3k views

Datediff() in MySQL

While calculating difference between 2 dates as number days, by using MySQL Datediff() function, I am facing some issue, the SQL code is given below. select datediff((select curdate()), (select ...
0
votes
1answer
667 views

Firebird-Time Interval- between 2 Dates

I try to select the period of employment for the members in a Company. Example: Date of join: 01.01.2001 Today: 27.02.2014 Now, I want this: 13 years, 1 month and 27 days I have a Statement which ...
0
votes
2answers
161 views

Primary key for link/junction table with time-dependent data

I want to link two tables in such a way that for any given time period the relationship is one-to-one, but over time the relationship can change. As an minimal example, suppose I want to list the ...
1
vote
1answer
1k views

SSIS: Get Date for Day of the Week

In SSIS, how do I get the date for a specific day of the week? For example, how do I get last Sunday's date? I assume I have to do some subtraction on the day-of-week, but not sure: DATEADD("d", ...
1
vote
2answers
140 views

How to use a column's latest date in query?

SELECT smart_allusers_karen.lastlogin, smart_allusers_karen.supemail, smart_allusers_karen.regionname FROM smart_allusers_karen WHERE lastlogin < MAX(smart_allusers_karen.lastlogin) - INTERVAL 30 ...
1
vote
2answers
3k views

how to change the default date format in oracle

New to Oracle here, i am working on some assigments but i noticed when i was creating the tables using a script, oracle throws the following error: ORA-01858: a non-numeric character was found where ...
0
votes
1answer
1k views

Get max date for each year from list of dates

Need to get max date for each year from list of arbitrary dates: date year ================= 1/10/2013 2013 3/26/2013 2013 4/20/2012 2012 9/10/2012 2012 5/6/2011 2011 So ...
16
votes
3answers
17k views

How to combine date and time to datetime2 in SQL Server?

Given the following components DECLARE @D DATE = '2013-10-13' DECLARE @T TIME(7) = '23:59:59.9999999' What is the best way of combining them to produce a DATETIME2(7) result with value '2013-10-13 ...