The tag has no wiki summary.

learn more… | top users | synonyms

3
votes
2answers
486 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. ...
0
votes
0answers
17 views

Get milliseconds from a DateTime in Sqlite3?

I have this DateTime in my database: 20130420070028401 is suppose to be: 2013-04-20T07:00:28.401 To "get" the milliseconds from 1970 I'm sub-stringing the DateTime: ...
5
votes
2answers
84 views

Is it possible to change the default style used by CAST/CONVERT when converting DATETIME? [closed]

When you attempt to CAST a DATETIME as VARCHAR in SQL Server, the default date formatting style is a fairly inaccurate date format that loses the precision of both seconds and milliseconds. For ...
1
vote
1answer
195 views

How to remove leading zero from date [closed]

I have a query in which i have used the following date_add(DATE_FORMAT(ss.week_startdate,'%y-%m-%d'),INTERVAL (tt.day_id- 1 )DAY) as date Datatype of the field is datetime and the result date is ...
0
votes
1answer
21 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 ...
2
votes
3answers
164 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 ...
0
votes
1answer
10 views

Building test site, update dates (back date records)

I have 3 tables that all connect and I need to back date them in an MS SQL 2008 setting. I was thinking about how to do this and not sure if I am on the correct path. The big problem I have is I have ...
-3
votes
1answer
21 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?
1
vote
1answer
41 views

SQL DATEDIFF coercion differences between databases on same SQL instance?

I have a data coercion mystery. I am seeing two different behaviors from DATEDIFF for the same query, and I cannot understand why. This is an extract of the relevant part of the query in question, ...
3
votes
1answer
79 views

How to check the timezone for a given datetime?

A database / table I'm working it saves all its date values as datetime (without a timezone). How can I find out which timezone that date was in? For instance, the Central European Summer Time (+02) ...
0
votes
1answer
67 views

Converting date string to SQL DATETIME - hopefully without a udf

I am taking an Excel file that has dates in different formats but one of these formats is giving me some grief when I try converting it or casting it to a SQL DATETIME. For example: Thu Oct 16 ...
3
votes
1answer
161 views

Cannot SUM datetime field in SQL Server migrated from Access

I am new here and am new to SQL Server (Express). I used the SSMA tool and it converted Access date/time fields to datatime2. The data contains duration data so there is no need for a date. I want to ...
0
votes
2answers
47 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
60 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 ...
0
votes
1answer
105 views

Unable to update or insert values table with DateTime column defaulted to GETDATE

I have atlered table by adding Datetime column. This column is not null and also default value from GETDATE(). ALTER TABLE myTable ADD myDateColun DATETIME NOT NULL DEFAULT (GETDATE()) I'm trying ...
0
votes
0answers
26 views

Ascii Symbols are Shown In varchar and Datetime Column Values of Mysql

Some rows in my database are shown in binary format. DateTime values are shown as below in Sqlyog. DC1203-68-58 51:18:72 Æ420-94-57 38:13:76 \722-49-30 63:17:35 ETB801-61-42 37:69:60 When i ...
0
votes
1answer
29 views

Calculations involving thresholds between two tables

I have a table comment and a table price like below. The key columns in both tables are ticker_id, price_datetime and price_open. The last column threshold in table comment (which currently NULL) is ...
3
votes
1answer
3k views

SQL Server Isnull Returning 1900-01-01 when field is null

I am using SQL Server 2012. The following piece of code is returning 1900-01-01 when the DOB field is null. I wanted (and expected) it to return an empty string ('') but it's not. How should I ...
0
votes
2answers
84 views

SAP Sybase IQ 16: Missing DATEROUND()

I'm just wondering what might can be wrong I was trying to round a date by its quarter. Beside some hacks I found the function DATEROUND, which I was thinking of based of documentation is able to do ...
1
vote
2answers
73 views

MySQL to SQL Server - Unixtime being major problem

I'm trying to convert this query to SQL Server. It already has a lot of updates. Still, I don't know what to do from here ( i'm not the best when it comes to programming in MySQL ) SELECT ...
0
votes
1answer
107 views

MySQL 5.6 Datetime Range

According to MySQL 5.6 doc, the supported range for datetime data type is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. But when I tried inserting values that are earlier than '1000-01-01 00:00:00', ...
4
votes
4answers
10k views

Conversion of a varchar data type to a datetime data type resulted in an out-of-range value

I am trying to run a simple query to get all rows created in November: SELECT COUNT(*) FROM dbo.profile WHERE [Created] BETWEEN '2014-11-01 00:00:00.000' AND '2014-11-30 23:59:59.997'; SMSS ...
1
vote
0answers
94 views

MySQL calculate overlapping period excluding overlapping rules

I would like to calculate the period overlap in hours based on special rules. I created a test setup, that works as long as the rules themselves don't overlap. Example non overlapping rules: id ...
1
vote
2answers
273 views

Converting string to date with timezone adjustment

I am attempting to convert stirngs to dates in mysql Thu Oct 23 16:46:47 2014 +02:00 Im unsure how to handle the +02:00, The rest is simple enough update logs set date = str_to_date(date_raw, ...
0
votes
1answer
87 views

DateTime returning with AM/PM

I have a SQL Server database (2008R2) running on a Windows 2003 box. I'm connecting to it using another Win2003 box. When I select a datetime value from the database, I'm getting it back in this ...
0
votes
1answer
47 views

MySQL optimize range duplicate query

I need to eliminate duplicates from a time sheet. I found this solution and have adapted it for my own needs: DROP TABLE IF EXISTS `activity`; CREATE TABLE IF NOT EXISTS `activity` ( `id` int(11) ...
0
votes
2answers
5k views

Grouping data by MONTH on DATETIME column in SQL Server

I have a table Employee which has the columns Id, Date of joining and Name Date of joining is a DATETIME column. I want to know how many users have joined in the month of October?
2
votes
1answer
239 views

Breaking down by minutes in between two dates

In MySQL, I've got a data model with a "visit" table that has start and end datetimes. How can I get a breakdown of the number of visits that are active per minute? e.g. | visit_id | start | end ...
0
votes
3answers
143 views

How do I unit test queries which are based on dates from now?

I've had a hard time making unit test for most of my queries because they are tied very closely to current dates and data which is added to the database could modify the result of the unit test. For ...
0
votes
1answer
80 views

Select Statement to generate result, for Group by per second and link with Table

i tried to search in existing queries but didnt find any related thread. i would like to run a query in user table (for example), and like to get output result on per second bases. and give me zero ...
1
vote
3answers
55 views

Get seconds of day in Postgres

In Java we use Joda-Time to get the seconds of a day as an int value (despite the date): date: 10-10-2014 00:00:30 -> second 30 of day date: 11-10-2014 00:01:30 -> second 90 of day date: ...
1
vote
1answer
219 views

First date in a week, not exceed from the month, start from specific day

I want to get first date of week from date, but not exceed from first date of month. This is the query i've tried : SET @date:='2014-10-03'; SELECT IF(DATE_ADD(@date, INTERVAL(1-DAYOFWEEK(@date)) ...
1
vote
1answer
164 views

Format timezone to +-#### instead of +-##

I am curious if there is a way to configure Postgres (9.1+) to format the TZ part of a "timestamp with time zone" as +-####, just like PHP's date.format method does using "O". PHP Docs: O | ...
2
votes
1answer
262 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 ...
3
votes
1answer
483 views

How to find XXth day of previous month in SQL server?

I have 2 parameters, an input date (DATETIME) input day of month (TINYINT) If I enter 11 MAR 2014 as the DATETIME and 26 as the input day of month, I would like to select 26 FEB 2014 as the ...
5
votes
2answers
4k views

MySQL performance problem using indexed datetime column

I tried to solve the following problem for about one hour now and still didn't get any further with it. Okay, I have a table (MyISAM): ...
0
votes
1answer
284 views

Case always returns null?

I have a subquery that gets the DATEDIFF of two dates. However the first case always returns null yet the actual sub query returns dates?! CASE WHEN [date1] > [date2] THEN ...
1
vote
1answer
499 views

How does Sybase store datatime information?

We have a Sybase database that currently stores datetime values in some of the tables. If we move that server with the Sybase database to another country which is in another time zone, will the ...
1
vote
0answers
391 views

Why does this MySQL date_sub statement return wrong values?

I had a complex statement that was returning strange values and I traced it to the following line: SELECT id, updated FROM status WHERE updated > date_sub(NOW(), INTERVAL 6 MINUTE) ORDER BY ...
2
votes
1answer
161 views

Optimal table design for a medical center rental service and recurrent shifts

I'm trying to figure out the optimal design for the following situation I'm being presented. I'm developing a medical web application with MySQL as db engine. A medical center has 14-16 offices/rooms ...
6
votes
4answers
268 views

Store millions of rows of denomalized data or some SQL magic?

My DBA experience doesn't go much further than simple storage + retrieval of CMS style data - so this may be a silly question, I don't know! I have a problem whereby I need to lookup or calculate ...
0
votes
0answers
119 views

Cohort analisis for gamers activity

I want to do some cohort analysis of gamer activity in my games. By cohort I mean some set of users who registered (or had first activity) on some particular date. For example: users registered on 16 ...
0
votes
1answer
470 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 ...
3
votes
2answers
452 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
1k 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 ...
1
vote
1answer
11k views

Understanding datetime formats in SQL Server

I've dealt with MS SQL Server datetime types for a long time but never thought why the following is happening: I query a table that contains a smalldatetime column. This smalldatetime is always ...
2
votes
2answers
327 views

Select different time

I looked on the internet and I found your post on the stackexchange. I will try to explain in more detail what we really need I do not know well to write English It is a system for airporttaxi id ...
1
vote
1answer
180 views

merge rows based on column

I have attendance_log table : +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
0
votes
2answers
64 views

Query to test and return timed lockout in MySQL

I have some functionality in my webapp that locks out users from certain behavior for a set amount of time (e.g., they have to wait 10 minutes before they can update a level). My query looks like ...
0
votes
1answer
122 views

Subtracting dates give me error in Postgresql

I use age to get the interval between to date fields. Their type is timestamp. My code is as below: select age(timestamp MySecondDateField, timestamp MyFirstDateField) The data in my field ...