Tagged Questions

The tag has no wiki summary.

learn more… | top users | synonyms

0
votes
3answers
123 views

Split date/time range into day ranges [closed]

Using this data (also shown in this fiddle): create table dbo.records ( id int not null primary key identity(1, 1), startdatetime datetime not null, enddatetime datetime not null ); insert ...
1
vote
1answer
31 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
53 views

Importing external data with specific time format into MS Access

I need to import data from a flat file to MS Access. Part of the data is time information in the format HH:MM:SS.SSSS (i.e. 24-hour time, fractional seconds). I have tried all of the standard time ...
1
vote
2answers
547 views

Converting a varchar date into a date (dd/mm/yy)

I have a CSV file that has dates written like this: 250909,240909 and they get stored in a SQL Server database as varchars. How do I get it to convert them into dates so that it gets displayed like ...
3
votes
1answer
64 views

Should I change my old datetime columns from SQL Server 2005 to just date datatype for SQL Server 2012

My database was developed in 2005 for SQL Server 2005 version. In that time for columns which store pure date without time we used datetime data type. Our database has survived up today and now we ...
1
vote
1answer
4k 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 ...
-1
votes
1answer
85 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
194 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 ...
1
vote
2answers
938 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 ...
2
votes
1answer
77 views

DATETIME2FROMPARTS alternatives for SQL Server 2008

I am trying to part a datetime value and build it together removing minutes, seconds and so on. I want the values to be hourly. Usually this works on a SQL Server 2012 machine: DATETIME2FROMPARTS( ...
0
votes
1answer
348 views

Date of birth saving to MYSQL DB with timestamp or date column type [closed]

i am using MYSQL database and would like to know which of this is a good practice. way 1 - use column type in DB as timestamp. i.e will contain values like 2012-11-13 00:00:00 way 2 - use column as ...
0
votes
2answers
104 views

How to guess or recover a timestamp conversion in sqllite?

I am in the middle of tiny forensic effort =) I am trying to transfer transactions from Money IQ (IOS) to My Wallet+ (IOS) as both apps use sqlite. Money IQ uses a standard unixepoch timestamp. ...
0
votes
2answers
2k views

Update year alone in date (Oracle 11g)

When I checked one of my client's database, I found some mistakes which will make problems. The problem is that the date is showing as 01-01-01 when I try to display it in the dd-MM-yyyy format the ...
0
votes
1answer
1k views

Different dates Oracle 11g with TOAD

I have the following queries: SELECT to_date(to_char(to_date('01-FEB-1949'))) FROM DUAL; /*this returns 2/1/2049. */ SELECT to_date(to_char(to_date('01-FEB-1949'),'dd-MON-yyyy')) FROM DUAL; ...
2
votes
1answer
880 views

Load different date formats in mysql

I have a date field in my datafile which I am trying to load in MySQL, but the date field is in dd-mon-yy format and MySQL only understands YYYY-MM-DD. Is there a way by which I can load DD-MON-YY ...
0
votes
3answers
862 views

SQL Server 2000 dateformat permanent change

I am using SQL Server 2000 for an old system web site. I've just hit an issue that is going to cause me endless hassles down the road. When I installed the server on my local system I took all the ...
2
votes
1answer
487 views

Setting DATEFIRST to 6

I would like to set FIRSTDATE to 6 (ie: saturday), i have read that to change it parmanently i could set the default language of the login to one of an existing language from the sys.syslanguages and ...
0
votes
1answer
9k views

How to get only month from MySQL date/time?

In my MySQL server, I have rows that have the date of my created content. Example: 2013-03-17 02:53:47 For now, I am using: $date = $fetch[thedaterownamegoeshere]; <?php echo $date; ?> But ...
1
vote
1answer
1k views

Getting the first day of week

In continuation of my previous question: Get week/month data I have a MySQL table with day column of type DATE and position column of type INT. I need to fetch average position for every week in ...
3
votes
1answer
4k views

how to retrieve rows of different dates using IN clause

My table structure: +-----------------+--------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
0
votes
1answer
97 views

UTC date in DMV dm_broker_queue_monitors

It seems that the DMV dm_broker_queue_monitors returns UTC time rather than local time. Why is it so? I am wondering how to get local time from this view? I can get current time offset, but that is ...
3
votes
1answer
467 views

Retrieving queries when the hour of starting timestamp is less than the hour of ending timestamp

I have a CTE-based query in which I retrieve hourly intervals between two given timestamps. My query works as following: Getting start and end datetimes (let's say 07-13-2011 10:21:09 and 07-31-2011 ...
2
votes
2answers
11k views

Comparing date and timestamp variables in a where clause in a procedure

I have a variable in an Oracle stored procedure of DATE type (from user interface), and another of TIMESTAMP type (database is more precise than what the user would enter in the code that is executing ...
2
votes
3answers
4k views

Date format in SSRS

I'm wondering if there is a setting in SSRS to indicate the date format. We need to show the dates in dd/mm/yyyy format on the reports and from what I can see in database the value has been stored ...
1
vote
1answer
2k views

How to convert execution time to a specific format?

I am working on a report that allows people to print off data for physical signatures. Part of the requirement is to format the exectuion date in the format DDMONYY (27Aug12). I can not find a way ...
0
votes
2answers
418 views

Convert datetime to europe default without time

I have a datetime field that I need to convert into the format DDMONYY (27Aug12). I am currently using convert(nvarchar(30),DateToConvert,13), but that also spits out the time. How can I easily ...
0
votes
1answer
2k views

Converting Python's date format to a MySQL

First of all I hope this is the right place to ask. I have a Python script which parses data from a few links. In the parsers I parse also the date of the last modified article. This is the output ...
0
votes
3answers
240 views

Work out date difference when there is only one date

I have 2 records, one details when the stop clock was stopped and the other started. I only have the one date value in each record. I'm trying to work out the difference between the stop and start ...
2
votes
3answers
1k views

SQL Server dates - can I get date and hour?

In SQL Server, can I do the equivalent of to_char(date_field, 'YYYY-MM-DD:HH24') in Oracle to get 2012-01-30:23?
5
votes
1answer
4k views

How to set Postgresql database to see date as “MDY” permanently

Ho can I set my database to see the 'date' as "MDY" without running: SET datestyle = "ISO, MDY"; every-time I'm trying to access it? I'm using Postgresql version 9.1, Ubuntu 12.04. My system ...
2
votes
2answers
4k views

Setting NLS params in logon trigger don't work in oracle 11g

We have a trigger setup so that when a specific user logs in, their NLS session parameters are modified. This used to work on Oracle 10g. We've just migrated to Oracle 11g RAC, and the session ...
1
vote
1answer
1k views

Decode query truncates a date

I am not sure if I am looking at this problem the right way: I have a query like this on an Oracle database SELECT DECODE( TO_CHAR(MIN(DATE), 'YYYYMMDD') , '' , NULL ...
2
votes
1answer
8k views

sybase format datetime yyyymmdd

This seems like something trivial would be easy, but I couldn't find a good way to do it. This is what I came up with: select convert(numeric, (select ...
1
vote
1answer
134 views

How to prepare a date and double for a mysql replace statement?

I have a MySql table with three columns: CU VARCHAR(3) DA DATETIME VA DOUBLE I need to execute several REPLACE into mytable (CU, DA, VA) values("ABC", mydate, myval) statements from Java. mydate is ...
0
votes
4answers
12k views

Convert a Unix timestamp to a DATETIME in a View

I have a table that stores a unix timestamp. To query this as a date, I'm attempting to convert this timestamp to a datetime type in a view. Unfortunately, I can only seem to get the DATE portion out. ...
0
votes
1answer
4k views

MySQL: Why is DATEDIFF and TIMEDIFF/60.0/60.0/24.0 results different?

I have a database with two DATETIME values, and then I generate some new values from them: tdiff = timediff(d1,d2)+0 day1 = truncate(tdiff/60.0/60.0/24.0,0) day2 = datediff(d1,d2)+0 All three of ...
2
votes
1answer
6k views

Compare a date and a timestamp with time zone with now() in the same query?

I have multiple database servers I'm querying with a query that compares an expiration column with now(). The problem is that one of the servers' expiration column is a timestamp with time zone, and ...
1
vote
1answer
159 views

OVERLAPS for Informix

Does Informix have a function equivalent to OVERLAPS, which finds out whether or not two time intervals have any time in common?
8
votes
3answers
4k views

Storing date as integer (numeric), what are the advantages

Question 1 I am working with a system where date is stored as integer (actual numeric(8,0)) and I have noticed that other systems also store date as int such as cisco in this thread. Example ...
3
votes
4answers
1k views

Best pratice to store dates group by months-year key par value

I am making one service which needs to hold data grouped and calculated by year-month combination. I know how to calculate data and put it on new table. But I am confused what kind of data type I ...
1
vote
1answer
4k views

YYYY-MM-DD has changed to YYYY-DD-MM after moving SQL Server to new domain how do I switch back?

I have moved the domain of my SQL Server and while everything is mainly working the date format for queries seems to have changed. So if I run this: select convert(datetime,'2012-02-13') I get ...
2
votes
1answer
176 views

Varchar to date in SQL Server 2008 [closed]

I have varchar data in database table which stores date in DD-MM-YYYY format. I want to select that varchar in MM-DD-YYYY format. I'm using select convert (varchar(30), (select CONVERT(datetime, ...
3
votes
3answers
3k views

Oracle Query by Quarter

I have a query that I want to get data from the past 5 quarters, but it doesn't work the way it should: select to_char(eventdate,'YYYY') || ' Q-' || to_char(eventdate, ' Q'), ... from ... where ...
5
votes
2answers
1k views

Best way to handle dates prior to 1000 A.D. in MySQL?

I am creating a database for records that extend prior to 1000 AD, but MySQL Date and DateTime fields only support dates starting at 1000. Is there a way that would be more convenient than either ...
7
votes
2answers
21k views

How to get the timestamp column in only milliseconds from PostgreSQL?

I have a column "created" with type timestamp without time zone default now() in a PostgreSQL database. If I select colums, it has a nice and readable format per default: SELECT created FROM ...