for questions specific to data types that contain both date and time parts.

learn more… | top users | synonyms

4
votes
1answer
79 views

Datetime as date only

I understand SQL Server 2005 doesn't support DATE and so casting a datetime as a date is not possible. What I want to do is list all records with a datetime within an entire day of the current week. ...
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
1answer
52 views

SQL Server comparing dates algorithm

How is the comparison between dates (with times) actually implemented? Consider the following query for example: SELECT * FROM dbo.Table1 WHERE DateTimeValue >= '2016-01-01 00:00:000' AND ...
1
vote
1answer
37 views

Help with adding a time filter to Postgres query

I've asked this question over in SE GIS, but told it would be better suited to this SE, so here goes... I know Postgres is brilliant for grouping points based on their spatial values - that I can do ...
0
votes
0answers
25 views

Date is not sorted in reporting services consecutively and correctly

I am trying to generate a report using the SSRS,but the date is not being sorted as I want when running the query. It is being shown as follow (consecutive months are mixed): 28/05/2014 ...
2
votes
1answer
30 views

Find Difference In Time

I am attempting to write a stored procedure to determine the difference in seconds between two times. If I were to hardcode the values I know I can use CAST(datediff(second, [start], [end])/3600.0 ...
0
votes
1answer
24 views

select rows older then column DATETIME - INT value of other column in single query

I have 2 columns in my table: (DATETIME)lastsendattempt (INT)sendgap I know I can select all rows that have a "lastsendattempt" DATETIME which is older then the current time. But, am I able to use ...
3
votes
1answer
87 views

SQL Server: How to add all dates of the month?

I need help. I am trying to add all dates for the month. I have this information: Month | Year ============= 1 | 2012 2 | 2012 3 | 2012 4 | 2012 I need to add all dates of the month ...
0
votes
1answer
36 views

Select different dates from mysql

I have a database structure like this ID | title | startTime | endTime And what I need is to get 3 records from the database where the first recorder is following this structure DAY(NOW())-1 the ...
0
votes
1answer
33 views

How to get current time in Oracle, and use in interval partition?

I'm trying to create an interval partitioned table in Oracle where the first partition is bounded to a week from now, but I just can't seem to figure out the syntax. Here's what I have so far.. ...
1
vote
1answer
39 views

How to make a unique datetime without seconds?

I need to build a table accepting one row for each minute. If I set the type of primary key to datetime, 60 records can be added for each minute. Is there any trick to design a table to keep ...
2
votes
1answer
171 views

Datetime Clustered Index Keeps getting fragmented

I have a large table (4 million Records) named CDR, which is used for storing CDRs (Call Detail Record)s from a Cisco router in a VoIP system, the table is constantly growing and will never have a ...
1
vote
0answers
13 views

SQL Server : implicit calculation of precision/scale for datetime scalar

I'm working with an application that ( for whatever reason... ) is unable to pass datetime stamps into its SQL Server database. Where the application needs a datetime stamp, it will push the server ...
1
vote
1answer
82 views

Query to get total time logged in per week

I need to find the number of hours each user is logged into an application per week. The table structure is as follows. There can be many logins/logouts per day. Any suggestions where to start? I'll ...
0
votes
2answers
41 views

Getting 30 minute slots between time

I have a Start Datetime and a End Datetime. Eg: 10:00 am - 12:00 pm I have to create 4 slots between the, each ranging for 30 minutes. Eg: 10:00 am-10:30 am 10:30 am-11:00 am 11:00 am-11:30 am ...
0
votes
1answer
66 views

How to find the price based on the given range

Here i need to find the price of the qty based on which price range the qty lies. In the first date(2015/01/01) 5 quantities have been sold and we made entries from (1 to 5) ,On second day for 10 ...
1
vote
1answer
61 views

Search efficiency of datetime vs date + indexed smallint (PostgreSQL)

Context We are storing weather forecasts as multiband rasters (using Postgres with PostGIS). Each forecast is identified by its date and time (either 0:00, 6:00, or 12:00). We are looking at about 20 ...
6
votes
1answer
49 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 ...
17
votes
3answers
392 views

Access (Jet) SQL: DateTime stamps in TableB flanking each DateTime stamp in TableA

First Words You can safely ignore the sections below (and including) JOINs: Starting Off if you just want to take a crack of the code. The background and results just serve as context. Please look at ...
0
votes
1answer
42 views

Filter column by records matching a timezone in Postgres

I want to check whether any rows in my column (TIMESTAMP WITH TIME ZONE) are not stored using UTC. Is there a query I can write to match values that are stored with a TZ offset of 1 or 8 or 11 or (not ...
-2
votes
1answer
38 views

Last date SQL Server query required

Scenario: I am writing a cash management system and want to put some date columns for getting date info / history. Actually there are two columns for date in same table at this moment. Voucher Date ...
1
vote
0answers
68 views

mysql datetime timezone +0000

I've got a Twitter database and some columns in multiple tables are time-stamps like: Tue Sep 09 23:48:37 +0000 2014 These columns are TEXT so I want to convert them. I read Date and Time Types, ...
1
vote
1answer
37 views

Time difference

I was working with SQL and I found something unusual. I was calculating the time difference between two datetimes in h:m:s format using SQL select time_format(timediff( '2015-09-12 18:47:15', ...
1
vote
2answers
244 views

Default value for “microtime” column in MySQL

My interest is to store the time with FSP of 6. As I've read, this cannot be achieved with TIMESTAMP or DATETIME data types. So, I have a double field to store the output of the microtime function. ...
1
vote
0answers
390 views

MySQL - Truncated incorrect datetime value on update statement

I have an application which produces log files. These log files include a Timestamp field in the format 2015-08-25T09:35:01 UTC. (there will be apx 60 logs produced at 0.25 GB per day). I need to ...
2
votes
1answer
79 views

How to add DATETIME value for session user

I am maintaining two tables: Employee table consists of username and password(for logging in) log table consists of login and logout time for the session user With the click of a button, I should ...
0
votes
1answer
37 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 ...
1
vote
2answers
49 views

Group by month in result columns?

I'm using this DBMS: Caché I've never used a pivot table in SQL and I'm not even good at it in Excel, but I assume that's what I need. Given the following sample data: ID Name Result Date ...
1
vote
1answer
54 views

Selecting 1st and 3rd Tuesdays

So here is my situation. I am trying to write a script which will (among other things) allow me to measure prime time block utilization. For reasons unknown to and unchangeable by me, various ...
1
vote
0answers
190 views

Select records where difference between datetime column1 and column 2 is greater than X amount of time

As the title indicates, I'm trying to return records where the datetime difference between two datetime columns is greater than X amount of time. I'm having trouble wrapping my head around this ...
10
votes
4answers
456 views

Why does my query search datetime not match?

select * from A where posted_date >= '2015-07-27 00:00:00.000' and posted_date <= '2015-07-27 23:59:59.999' But the result contains a record that has posted_date today: 2015-07-28. My ...
2
votes
3answers
177 views

updating the entire column with datetime values based on other values in the row

I have a very large table [server].[dbo].[MyTable] where I need the SQL statement to fill the column 'ToDateTime' (currently full of NULLs) according to the following logic: Only add a value to ...
4
votes
2answers
605 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 ...
3
votes
1answer
292 views

current_timestamp default column value working in Windows but not in Linux

This MySql query runs fine in Windows: CREATE TABLE `posts`.`table` ( `idTable` INT NOT NULL AUTO_INCREMENT, `Column1` DATETIME NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`idTable`)); In ...
2
votes
1answer
79 views

How to make a query using “where MONTH(datetime)” faster?

I already tried to add an index on the datetime column, but the result is still unacceptably slow. SELECT s.id, s.player, COUNT(case when dg.winner = 1 AND dp.colour <= 5 then 1 when dg.winner = 2 ...
1
vote
1answer
51 views

Weed out certain rows where timestamp is within interval

Given a table structure: create table contacts ( contact_id int(8) not null auto increment, #primary key pid int(8) not null, #personal id contakt_time datetime, ...
3
votes
1answer
163 views

Leap second in Database system (Postgresql and SQL Server)

This night will be longer because of leap second: https://en.wikipedia.org/wiki/Leap_second My impact will be minimal in my case, I don't even know if I will recive some weird data like 20150630 ...
1
vote
1answer
73 views

Intervals query of huge data

My question is more or less theoretical - what is the best approch to get query data by intervals. The situation I have a system that manage counters (thousands of counters - counts consumption of ...
0
votes
1answer
290 views

how to extract current year and combine with specific month and date as a date

I have to get the current year with June the 16th as a date. For example, if I run the query now I want to get 2015-06-16. I tried the following: CONVERT(DATETIME,cast( (cast(YEAR(GetDate()) AS ...
0
votes
0answers
47 views

Syntax to change a DB2 timestamp from CST to EST

I am writing a code and the source are two DB2 databases. I see that both of them have a different timestamps. (one shows CST and another is EST). I need to make sure that both of them have similar ...
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. ...
0
votes
0answers
136 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
381 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
529 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
231 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 ...
4
votes
3answers
7k 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
22 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 ...
-4
votes
1answer
28 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
161 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
490 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) ...