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

learn more… | top users | synonyms

0
votes
1answer
36 views

Date Conversion error

This is just an example: SELECT * FROM A.B where B_DATE='19-05-2016 12.00.00.000000000 AM'; I always get an error: Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date ...
0
votes
0answers
10 views

Issue in Datetime nodes in Importing data from XML files into SQL Server tables [closed]

I use this code as a part of my TSQL code to convert some XML file into SQL tables BEGIN declare @filepath NVARCHAR (1000) set @filepath = 'C:\Media\All\'+ CAST(@fname as varchar(300)) ...
0
votes
1answer
20 views

column values do not return properly when column name called out specifically in select statement

I have a database with this table (version 5.0.95): > describe shift; +-----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | ...
1
vote
1answer
39 views

If replication is 600 seconds behind master will INSERT or UPDATE queries including `NOW()` be 600s different?

If slave is behind master by 600 seconds, will queries including NOW() also be 600s different? Example: master|mysql> INSERT INTO my_table (hash,my_time) VALUES ('foo',NOW()); ### 600s later, ...
3
votes
3answers
199 views

How to return rows between two datetimes stored as date and time?

How to view the result between 2 dates and times? My current Code: SELECT * FROM [DB].[dbo].[TABLE] WHERE [USER_ID] = '005' AND [DATE] BETWEEN '03-04-2016' AND '04-04-2016' AND [TIME] >= ...
0
votes
1answer
38 views

How do I find the day of week of a date-time field in postgres?

How do I find the day of week of a date-time field? I'm trying to extract the the day of the week from a column in a sql query in postgres. While SELECT EXTRACT(DOW FROM '2011-07-07') works, the ...
-4
votes
2answers
45 views

Query orders in recent 5 years [closed]

I am using SQL Server, and I have an Order table with a column OrderDate (datetime). How can I write a query to retrieve all the orders in the past 5 years? My query: select * from Order where ...
0
votes
1answer
39 views

Why does this SQL work on one version of MySql-Server but not another?

I'm trying to run the following very simple SQL command on two different MySQL databases: CREATE TABLE mytable ( created DATETIME(6) NOT NULL, updated DATETIME(6) NOT NULL, my_id ...
-2
votes
1answer
50 views

Cannot update nor insert data to datetime field [closed]

I was trying to insert data through one web application I am developing and I could not insert data, while I was troubleshooting what the problem is, I saw that I can not neither edit datetime field ...
1
vote
1answer
76 views

Subtract two dates stored as varchar

I have 2 varchars like: 20160316131824 20160316080002 How can I subtract to see if there is more than 24 hours of difference between the 2 values? I tried using the symbol - but it gives me an ...
7
votes
1answer
129 views

Sum Interval of Dates within same column

How do you best sum the differences of a range of dates in the same column between interleaving rows? I have a Datetime column and want to calculate the difference between Rows. I want the difference ...
2
votes
1answer
33 views

Should future time be stored in UTC or local time?

Clearly in most cases time should be store in UTC. And in all cases a datetime that records a past event should be stored in UTC. We do this because civil time keeps changing and an event happened at ...
1
vote
1answer
73 views

Converting UTC time to timestamptz always adds the +01 offset in Postgres

In Postgres 9.5, I do the following to convert a timestamptz to the UTC timezone: SELECT TIMESTAMP WITH TIME ZONE '2016-02-20 15:15:54.000495+01' AT TIME ZONE 'UTC' this outputs correctly ...
2
votes
3answers
196 views

How to find the max date stamp in a database?

I want to find the most recent date in a database. I could use sys.dm_db_index_usage_stats to find when it was last updated, but there is no value here if it has not been updated since the last ...
4
votes
2answers
122 views

How to deal with datetime ranges in a table with separate columns for date and time?

In a table that stores events, the date and time are two separate columns: CREATE TABLE events ( pk int serial, detail text, ev_date date, ev time without time zone ); If I ...
0
votes
1answer
58 views

How to display the number of days elapsed between two dates denoted in columns-specifically those exceeding 30 days?

Perhaps I'm overthinking this Oracle SQL query, but I'm attempting to display the number of days elapsed between two column data-sets attributed as "order-date" and "ship-date", which will be ...
0
votes
1answer
53 views

Combine various temporal criteria to filter rows

I am trying to query a database table with some filters. I have a form through which a user will create query attributes: When user will click on search it will pass such values: {camera_id: "9879" ...
4
votes
1answer
109 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
40 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
76 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
43 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
47 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
34 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
51 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
109 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
45 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
42 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
48 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
207 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
20 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 ...
0
votes
2answers
71 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
82 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
72 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
61 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
401 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
63 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
48 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
76 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
39 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
606 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
780 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
117 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
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 ...
1
vote
2answers
62 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
58 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
383 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
676 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
582 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
722 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
657 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 ...