Search Results

Search found 19966 results on 799 pages for 'datetime query'.

Page 1/799 | 1 2 3 4 5 6 7 8 9 10 11 12  | Next Page >

• SQL SERVER – Information Related to DATETIME and DATETIME2

  - by pinaldave

  I recently received interesting comment on the blog regarding workaround to overcome the precision issue while dealing with DATETIME and DATETIME2. I have written over this subject earlier over here. SQL SERVER – Difference Between GETDATE and SYSDATETIME SQL SERVER – Difference Between DATETIME and DATETIME2 – WITH GETDATE SQL SERVER – Difference Between DATETIME and DATETIME2 SQL Expert Jing Sheng Zhong has left following comment: The issue you found in SQL server new datetime type is related time source function precision. Folks have found the root reason of the problem – when data time values are converted (implicit or explicit) between different data type, which would lose some precision, so the result cannot match each other as thought. Here I would like to gave a work around solution to solve the problem which the developers met. -- Declare and loop DECLARE @Intveral INT, @CurDate DATETIMEOFFSET; CREATE TABLE #TimeTable (FirstDate DATETIME, LastDate DATETIME2, GlobalDate DATETIMEOFFSET) SET @Intveral = 10000 WHILE (@Intveral > 0) BEGIN ----SET @CurDate = SYSDATETIMEOFFSET(); -- higher precision for future use only SET @CurDate = TODATETIMEOFFSET(GETDATE(),DATEDIFF(N,GETUTCDATE(),GETDATE())); -- lower precision to match exited date process INSERT #TimeTable (FirstDate, LastDate, GlobalDate) VALUES (@CurDate, @CurDate, @CurDate) SET @Intveral = @Intveral - 1 END GO -- Distinct Values SELECT COUNT(DISTINCT FirstDate) D_DATETIME, COUNT(DISTINCT LastDate) D_DATETIME2, COUNT(DISTINCT GlobalDate) D_SYSGETDATE FROM #TimeTable GO -- Join SELECT DISTINCT a.FirstDate,b.LastDate, b.GlobalDate, CAST(b.GlobalDate AS DATETIME) GlobalDateASDateTime FROM #TimeTable a INNER JOIN #TimeTable b ON a.FirstDate = CAST(b.GlobalDate AS DATETIME) GO -- Select SELECT * FROM #TimeTable GO -- Clean up DROP TABLE #TimeTable GO If you read my blog SQL SERVER – Difference Between DATETIME and DATETIME2 you will notice that I have achieved the same using GETDATE(). Are you using DATETIME2 in your production environment? If yes, I am interested to know the use case. Reference: Pinal Dave (http://www.SQLAuthority.com) Filed under: Pinal Dave, SQL, SQL Authority, SQL DateTime, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• SQL SERVER – Difference Between DATETIME and DATETIME2 – WITH GETDATE

  - by pinaldave

  Earlier I wrote blog post SQL SERVER – Difference Between GETDATE and SYSDATETIME which inspired me to write SQL SERVER – Difference Between DATETIME and DATETIME2. Now earlier two blog post inspired me to write this blog post (and 4 emails and 3 reads from readers). I previously populated DATETIME and DATETIME2 field with SYSDATETIME, which gave me very different behavior as SYSDATETIME was rounded up/down for the DATETIME datatype. I just ran the same experiment but instead of populating SYSDATETIME in this script I will be using GETDATE function. DECLARE @Intveral INT SET @Intveral = 10000 CREATE TABLE #TimeTable (FirstDate DATETIME, LastDate DATETIME2) WHILE (@Intveral > 0) BEGIN INSERT #TimeTable (FirstDate, LastDate) VALUES (GETDATE(), GETDATE()) SET @Intveral = @Intveral - 1 END GO SELECT COUNT(DISTINCT FirstDate) D_FirstDate, COUNT(DISTINCT LastDate) D_LastDate FROM #TimeTable GO SELECT DISTINCT a.FirstDate, b.LastDate FROM #TimeTable a INNER JOIN #TimeTable b ON a.FirstDate = b.LastDate GO SELECT * FROM #TimeTable GO DROP TABLE #TimeTable GO Let us run above script and observe the results. You will find that the values of GETDATE which is populated in both the columns FirstDate and LastDate are very much same. This is because GETDATE is of datatype DATETIME and the precision of the GETDATE is smaller than DATETIME2 there is no rounding happening. In other word, this experiment is pointless. I have included this as I got 4 emails and 3 twitter questions on this subject. If your datatype of variable is smaller than column datatype there is no manipulation of data, if data type of variable is larger than column datatype the data is rounded. Reference: Pinal Dave (http://www.SQLAuthority.com) Filed under: Pinal Dave, SQL, SQL Authority, SQL DateTime, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• SQL SERVER – Difference Between DATETIME and DATETIME2

  - by pinaldave

  Yesterday I have written a very quick blog post on SQL SERVER – Difference Between GETDATE and SYSDATETIME and I got tremendous response for the same. I suggest you read that blog post before continuing this blog post today. I had asked people to honestly take part and share their view about above two system function. There are few emails as well few comments on the blog post asking question how did I come to know the difference between the same. The answer is real world issues. I was called in for performance tuning consultancy where I was asked very strange question by one developer. Here is the situation he was facing. System had a single table with two different column of datetime. One column was datelastmodified and second column was datefirstmodified. One of the column was DATETIME and another was DATETIME2. Developer was populating them with SYSDATETIME respectively. He was always thinking that the value inserted in the table will be the same. This table was only accessed by INSERT statement and there was no updates done over it in application.One fine day he ran distinct on both of this column and was in for surprise. He always thought that both of the table will have same data, but in fact they had very different data. He presented this scenario to me. I said this can not be possible but when looked at the resultset, I had to agree with him. Here is the simple script generated to demonstrate the problem he was facing. This is just a sample of original table. DECLARE @Intveral INT SET @Intveral = 10000 CREATE TABLE #TimeTable (FirstDate DATETIME, LastDate DATETIME2) WHILE (@Intveral > 0) BEGIN INSERT #TimeTable (FirstDate, LastDate) VALUES (SYSDATETIME(), SYSDATETIME()) SET @Intveral = @Intveral - 1 END GO SELECT COUNT(DISTINCT FirstDate) D_GETDATE, COUNT(DISTINCT LastDate) D_SYSGETDATE FROM #TimeTable GO SELECT DISTINCT a.FirstDate, b.LastDate FROM #TimeTable a INNER JOIN #TimeTable b ON a.FirstDate = b.LastDate GO SELECT * FROM #TimeTable GO DROP TABLE #TimeTable GO Let us see the resultset. You can clearly see from result that SYSDATETIME() does not populate the same value in the both of the field. In fact the value is either rounded down or rounded up in the field which is DATETIME. Event though we are populating the same value, the values are totally different in both the column resulting the SELF JOIN fail and display different DISTINCT values. The best policy is if you are using DATETIME use GETDATE() and if you are suing DATETIME2 use SYSDATETIME() to populate them with current date and time to accurately address the precision. As DATETIME2 is introduced in SQL Server 2008, above script will only work with SQL SErver 2008 and later versions. I hope I have answered few questions asked yesterday. Reference: Pinal Dave (http://www.SQLAuthority.com) Filed under: Pinal Dave, SQL, SQL Authority, SQL DateTime, SQL Optimization, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• How to get full query string parameters not UrlDecoded

  - by developerit

  Introduction While developing Developer IT’s website, we came across a problem when the user search keywords containing special character like the plus ‘+’ char. We found it while looking for C++ in our search engine. The request parameter output in ASP.NET was “c “. I found it strange that it removed the ‘++’ and replaced it with a space… Analysis After a bit of Googling and Reflection, it turns out that ASP.NET calls UrlDecode on each parameters retreived by the Request(“item”) method. The Request.Params property is affected by this two since it mashes all QueryString, Forms and other collections into a single one. Workaround Finally, I solve the puzzle usign the Request.RawUrl property and parsing it with the same RegEx I use in my url re-writter. The RawUrl not affected by anything. As its name say it, it’s raw. Published on http://www.developerit.com/

  Read the article

• SQL SERVER – Find Weekend and Weekdays from Datetime in SQL Server 2012

  - by pinaldave

  Yesterday we had very first SQL Bangalore User Group meeting and I was asked following question right after the session. “How do we know if today is a weekend or weekday using SQL Server Functions?” Well, I assume most of us are using SQL Server 2012 so I will suggest following solution. I am using SQL Server 2012′s CHOOSE function. It is SELECT GETDATE() Today, DATENAME(dw, GETDATE()) DayofWeek, CHOOSE(DATEPART(dw, GETDATE()), 'WEEKEND','Weekday', 'Weekday','Weekday','Weekday','Weekday','WEEKEND') WorkDay GO You can use the choose function on table as well. Here is the quick example of the same. USE AdventureWorks2012 GO SELECT A.ModifiedDate, DATENAME(dw, A.ModifiedDate) DayofWeek, CHOOSE(DATEPART(dw, A.ModifiedDate), 'WEEKEND','Weekday', 'Weekday','Weekday','Weekday','Weekday','WEEKEND') WorkDay FROM [Person].[Address] A GO If you are using an earlier version of the SQL Server you can use a CASE statement instead of CHOOSE function. Please read my earlier article which discusses CHOOSE function and CASE statements. Logical Function – CHOOSE() – A Quick Introduction Reference:  Pinal Dave (http://blog.SQLAuthority.com) Filed under: PostADay, SQL, SQL Authority, SQL DateTime, SQL Function, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• DateTime.MinValue vs new DateTime() in C#

  - by MadBoy

  When getting SQL DateTime Resharper suggests to use new DateTime() when value is DBNull.Value. I've always used DateTime.MinValue. Which is the proper way? DateTime varData = sqlQueryResult["Data"] is DateTime ? (DateTime) sqlQueryResult["Data"] : new DateTime();

  Read the article

• How to convert a python utc datetime to a local datetime using only python standard library?

  - by Nitro Zark

  I have a python datetime instance that was created using datetime.utcnow() and persisted in database. For display, I would like to convert the datetime instance reloaded from database to local datetime using the default local timezone (e.g. as if the datetime was create using datetime.now()) How can I convert the utc datetime to a local datetime using only python standard library (e.g. no pytz dependency)? It seems one solution would be to use datetime.astimezone( tz ), but how would do you get the default local timezone?

  Read the article

• C# DateTime Class and Datetime in database

  - by Spyros

  Hello . I have the following problem. I have an object with some DateTime properties , and a Table in database that I store all that objects , in Sql server I want to store the DateTime properties in some columns of DateTime Datatype, but the format of datetime in sql server is different from the DateTime class in c# and I got an sql exception saying "DateTime cannot be parsed". I know how to solve this by making the format yyyy-MM-dd but is this the proper and best solution to do this?

  Read the article

• Converting Openfire IM datetime values in SQL Server to / from VARCHAR(15) and DATETIME data types

  - by Brian Biales

  A client is using Openfire IM for their users, and would like some custom queries to audit user conversations (which are stored by Openfire in tables in the SQL Server database). Because Openfire supports multiple database servers and multiple platforms, the designers chose to store all date/time stamps in the database as 15 character strings, which get converted to Java Date objects in their code (Openfire is written in Java).  I did some digging around, and, so I don't forget and in case someone else will find this useful, I will put the simple algorithms here for converting back and forth between SQL DATETIME and the Java string representation. The Java string representation is the number of milliseconds since 1/1/1970.  SQL Server's DATETIME is actually represented as a float, the value being the number of days since 1/1/1900, the portion after the decimal point representing the hours/minutes/seconds/milliseconds... as a fractional part of a day.  Try this and you will see this is true:     SELECT CAST(0 AS DATETIME) and you will see it returns the date 1/1/1900. The difference in days between SQL Server's 0 date of 1/1/1900 and the Java representation's 0 date of 1/1/1970 is found easily using the following SQL:   SELECT DATEDIFF(D, '1900-01-01', '1970-01-01') which returns 25567.  There are 25567 days between these dates. So to convert from the Java string to SQL Server's date time, we need to convert the number of milliseconds to a floating point representation of the number of days since 1/1/1970, then add the 25567 to change this to the number of days since 1/1/1900.  To convert to days, you need to divide the number by 1000 ms/s, then by  60 seconds/minute, then by 60 minutes/hour, then by 24 hours/day.  Or simply divide by 1000*60*60*24, or 86400000.   So, to summarize, we need to cast this string as a float, divide by 86400000 milliseconds/day, then add 25567 days, and cast the resulting value to a DateTime.  Here is an example:   DECLARE @tmp as VARCHAR(15)   SET @tmp = '1268231722123'   SELECT @tmp as JavaTime, CAST((CAST(@tmp AS FLOAT) / 86400000) + 25567 AS DATETIME) as SQLTime   To convert from SQL datetime back to the Java time format is not quite as simple, I found, because floats of that size do not convert nicely to strings, they end up in scientific notation using the CONVERT function or CAST function.  But I found a couple ways around that problem. You can convert a date to the number of  seconds since 1/1/1970 very easily using the DATEDIFF function, as this value fits in an Int.  If you don't need to worry about the milliseconds, simply cast this integer as a string, and then concatenate '000' at the end, essentially multiplying this number by 1000, and making it milliseconds since 1/1/1970.  If, however, you do care about the milliseconds, you will need to use DATEPART to get the milliseconds part of the date, cast this integer to a string, and then pad zeros on the left to make sure this is three digits, and concatenate these three digits to the number of seconds string above.  And finally, I discovered by casting to DECIMAL(15,0) then to VARCHAR(15), I avoid the scientific notation issue.  So here are all my examples, pick the one you like best... First, here is the simple approach if you don't care about the milliseconds:   DECLARE @tmp as VARCHAR(15)   DECLARE @dt as DATETIME   SET @dt = '2010-03-10 14:35:22.123'   SET @tmp = CAST(DATEDIFF(s, '1970-01-01 00:00:00' , @dt) AS VARCHAR(15)) + '000'   SELECT @tmp as JavaTime, @dt as SQLTime If you want to keep the milliseconds:   DECLARE @tmp as VARCHAR(15)   DECLARE @dt as DATETIME   DECLARE @ms as int   SET @dt = '2010-03-10 14:35:22.123'   SET @ms as DATEPART(ms, @dt)   SET @tmp = CAST(DATEDIFF(s, '1970-01-01 00:00:00' , @dt) AS VARCHAR(15))           + RIGHT('000' + CAST(@ms AS VARCHAR(3)), 3)   SELECT @tmp as JavaTime, @dt as SQLTime Or, in one fell swoop:   DECLARE @dt as DATETIME   SET @dt = '2010-03-10 14:35:22.123'   SELECT @dt as SQLTime     , CAST(DATEDIFF(s, '1970-01-01 00:00:00' , @dt) AS VARCHAR(15))           + RIGHT('000' + CAST( DATEPART(ms, @dt) AS VARCHAR(3)), 3) as JavaTime   And finally, a way to simply reverse the math used converting from Java date to SQL date. Note the parenthesis - watch out for operator precedence, you want to subtract, then multiply:   DECLARE @dt as DATETIME   SET @dt = '2010-03-10 14:35:22.123'   SELECT @dt as SQLTime     , CAST(CAST((CAST(@dt as Float) - 25567.0) * 86400000.0 as DECIMAL(15,0)) as VARCHAR(15)) as JavaTime Interestingly, I found that converting to SQL Date time can lose some accuracy, when I converted the time above to Java time then converted  that back to DateTime, the number of milliseconds is 120, not 123.  As I am not interested in the milliseconds, this is ok for me.  But you may want to look into using DateTime2 in SQL Server 2008 for more accuracy.

  Read the article

• Finding begin and end of year/month/day/hour

  - by reto

  I'm using the following snipped to find the begin and end of several time periods in Joda. The little devil on my left shoulder says thats the way to go... but I dont believe him. Could anybody with some joda experience take a brief look and tell me that the little guy is right? (It will be only used for UTC datetime objects) Thank you! /* Year */ private static DateTime endOfYear(DateTime dateTime) { return endOfDay(dateTime).withMonthOfYear(12).withDayOfMonth(31); } private static DateTime beginningOfYear(DateTime dateTime) { return beginningOfMonth(dateTime).withMonthOfYear(1); } /* Month */ private static DateTime endOfMonth(DateTime dateTime) { return endOfDay(dateTime).withDayOfMonth(dateTime.dayOfMonth().getMaximumValue()); } private static DateTime beginningOfMonth(DateTime dateTime) { return beginningOfday(dateTime).withDayOfMonth(1); } /* Day */ private static DateTime endOfDay(DateTime dateTime) { return endOfHour(dateTime).withHourOfDay(23); } private static DateTime beginningOfday(DateTime dateTime) { return beginningOfHour(dateTime).withHourOfDay(0); } /* Hour */ private static DateTime beginningOfHour(DateTime dateTime) { return dateTime.withMillisOfSecond(0).withSecondOfMinute(0).withMinuteOfHour(0); } private static DateTime endOfHour(DateTime dateTime) { return dateTime.withMillisOfSecond(999).withSecondOfMinute(59).withMinuteOfHour(59); }

  Read the article

• converting string to valid datetime

  - by Ranjana

  strdate=15/06/2010 DateTime dt = DateTime.Parse(strdate, System.Threading.Thread.CurrentThread.CurrentCulture.DateTimeFormat); i cannot able to get the datetime value as dd/mm/yyyy. it is giving exception 'string is not recognized as a valid datetime' oly if it is in 06/15/2010 it is working. how to get the same format in dt.

  Read the article

• SQL SERVER – Script to Find First Day of Current Month

  - by Pinal Dave

  Earlier I wrote a blog post about SQL SERVER – Query to Find First and Last Day of Current Month and it is a very popular post. In this post, I convert the datetime to Varchar and later on use it. However, SQL Expert Michael Usov has made a good point suggesting that it is not always a good idea to convert datetime to any other date format as it is quite possible that we may need it the value in the datetime format for other operation. He has suggested a very quick solution where we can get the first day of the current month with or without time value and keep them with datatype datetime. Here is the simple script for the same. -- first day of month -- with time zeroed out SELECT CAST(DATEADD(DAY,-DAY(GETDATE())+1, CAST(GETDATE() AS DATE)) AS DATETIME) -- with time as it was SELECT DATEADD(DAY,-DAY(GETDATE())+1, CAST(GETDATE() AS DATETIME)) Here is the resultset: Reference: Pinal Dave (http://blog.SQLAuthority.com)Filed under: PostADay, SQL, SQL Authority, SQL DateTime, SQL Function, SQL Query, SQL Server, SQL Tips and Tricks, T SQL

  Read the article

• SQL SERVER – Display Datetime in Specific Format – SQL in Sixty Seconds #033 – Video

  - by pinaldave

  A very common requirement of developers is to format datetime to their specific need. Every geographic location has different need of the date formats. Some countries follow the standard of mm/dd/yy and some countries as dd/mm/yy. The need of developer changes as geographic location changes. In SQL Server there are various functions to aid this requirement. There is function CAST, which developers have been using for a long time as well function CONVERT which is a more enhanced version of CAST. In the latest version of SQL Server 2012 a new function FORMAT is introduced as well. In this SQL in Sixty Seconds video we cover two different methods to display the datetime in specific format. 1) CONVERT function and 2) FORMAT function. Let me know what you think of this video. Here is the script which is used in the video: -- http://blog.SQLAuthority.com -- SQL Server 2000/2005/2008/2012 onwards -- Datetime SELECT CONVERT(VARCHAR(30),GETDATE()) AS DateConvert; SELECT CONVERT(VARCHAR(30),GETDATE(),10) AS DateConvert; SELECT CONVERT(VARCHAR(30),GETDATE(),110) AS DateConvert; SELECT CONVERT(VARCHAR(30),GETDATE(),5) AS DateConvert; SELECT CONVERT(VARCHAR(30),GETDATE(),105) AS DateConvert; SELECT CONVERT(VARCHAR(30),GETDATE(),113) AS DateConvert; SELECT CONVERT(VARCHAR(30),GETDATE(),114) AS DateConvert; GO -- SQL Server 2012 onwards -- Various format of Datetime SELECT CONVERT(VARCHAR(30),GETDATE(),113) AS DateConvert; SELECT FORMAT ( GETDATE(), 'dd mon yyyy HH:m:ss:mmm', 'en-US' ) AS DateConvert; SELECT CONVERT(VARCHAR(30),GETDATE(),114) AS DateConvert; SELECT FORMAT ( GETDATE(), 'HH:m:ss:mmm', 'en-US' ) AS DateConvert; GO -- Specific usage of Format function SELECT FORMAT(GETDATE(), N'"Current Time is "dddd MMMM dd, yyyy', 'en-US') AS CurrentTimeString; This video discusses CONVERT and FORMAT in simple manner but the subject is much deeper and there are lots of information to cover along with it. I strongly suggest that you go over related blog posts in next section as there are wealth of knowledge discussed there. Related Tips in SQL in Sixty Seconds: Get Date and Time From Current DateTime – SQL in Sixty Seconds #025 Retrieve – Select Only Date Part From DateTime – Best Practice Get Time in Hour:Minute Format from a Datetime – Get Date Part Only from Datetime DATE and TIME in SQL Server 2008 Function to Round Up Time to Nearest Minutes Interval Get Date Time in Any Format – UDF – User Defined Functions Retrieve – Select Only Date Part From DateTime – Best Practice – Part 2 Difference Between DATETIME and DATETIME2 Saturday Fun Puzzle with SQL Server DATETIME2 and CAST What would you like to see in the next SQL in Sixty Seconds video? Reference: Pinal Dave (http://blog.sqlauthority.com)   Filed under: Database, Pinal Dave, PostADay, SQL, SQL Authority, SQL in Sixty Seconds, SQL Query, SQL Scripts, SQL Server, SQL Server Management Studio, SQL Tips and Tricks, T SQL, Technology, Video Tagged: Excel

  Read the article

• Formatting Problem Date with DateTime

  - by Florian

  Hello, I want to display a date with this format : MM/dd/yyyy HH:mm:ss tt for example : 01/04/2011 03:34:03 PM but I have a problem with the following code class Program { static void Main(string[] args) { DateTime dt = new DateTime(DateTime.Today.Year, DateTime.Today.Month, DateTime.Today.Day, 0, 0, 0); string displayedDate = dt.ToString("MM/dd/yyyy hh:mm:ss tt", CultureInfo.InvariantCulture); Console.WriteLine(displayedDate); Console.Read(); } } displays : 01/04/2011 12:00:00 AM instead of 01/04/2011 00:00:00 AM Anyone knows why ? Thank you !

  Read the article

• sql - datetime variable versus string representation of datetime variable

  - by BhejaFry

  Hi folks, I have a query that takes too long to respond when the search parameter happens to be a varchar datatype with date. However, if i convert varchar to datetime variable, the query runs fine. For ex: This takes too long. select count(id) from names where updateddate '1/5/2010' This runs fine. declare @dateparam datetime set @dateparam = convert(datetime, '1/5/2010',102) select count(id) from names where updateddate @dateparam What's the reason one runs fine but the other doesn't? TIA

  Read the article

• SQL SERVER – DATE and TIME in SQL Server 2008

  - by pinaldave

  I was thinking about DATE and TIME datatypes in SQL Server 2008. I earlier wrote about the about best practices of the same. Recently I had written one of the script written for SQL Server 2008 had to run on SQL Server 2005 (don’t ask me why!), I had to convert the DATE and TIME datatypes to DATETIME. Let me run quick demo for the same. DECLARE @varDate AS DATE DECLARE @varTime AS TIME SET @varDate = '10/10/2010' SET @varTime = '12:12:12' SELECT CAST(@varDate AS DATETIME) C_Date SELECT CAST(@varTime AS DATETIME) C_Time As seen in example when DATE is converted to DATETIME it adds the of midnight. When TIME is converted to DATETIME it adds the date of 1900 and it is something one wants to consider if you are going to run script from SQL Server 2008 to earlier version with CONVERT. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: SQL, SQL Authority, SQL DateTime, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• php convert european datetime to mysql datetime

  - by Mathlight

  I'm really stuck with this problem. I've got an datetime string like this: 28-06-14 11:01:00 I'm trying to convert it to 2014-06-28 11:01:00 so that i can insert it into the database ( with field type datetime. I've tryed multiple things like this: $datumHolder = new DateTime($data['datum'], new DateTimeZone('Europe/Amsterdam')); $datum1 = $datumHolder -> format("Y-m-d H:i:s"); $datum2 = date( 'Y-m-d', strtotime(str_replace('-', '/', $data['datum']) ) ); $datum3 = DateTime::createFromFormat( 'Y-m-d-:Hi:s', $data['datum']); This is the output i get: datum1: 2028-06-14 11:01:00 datum2: 1970-01-01 And i get an error for datum3: echo "datum3: " . $datum3->format( 'Y-m-d H:i:s'); . '<br />'; Call to a member function format() on a non-object So my question is very clear... What am I doing wrong / how to get this working? Thanks in advantage guys! I know that this question is asked many, many times... But whatever i try, i can't get it working...

  Read the article

• Compare Created DateTime to DateTime.Today at 6pm, C#

  - by Refracted Paladin

  In C# I need to compare the value of DateTime.Today /6pm, to a field that stores the Created DateTime. Basically there is certain functionality that is only accessible on the same day as the created day and then only till 6pm. The part I am not fully understanding is how to accurately represent 6pm on Today to compare against. Is there a method that always returns, say, Midnight that I can then do a .AddHours(18); to? Am I over-complicating this? Thanks.

  Read the article

• how to convert the datetime value to hour

  - by Ranjana

  i have datetime value as 2010-04-07 09:00:00.000 2010-04-07 14:30:00.000 how to convert the 14:30 to 2:30 pm

  Read the article

• SQL University: Parallelism Week - Part 2, Query Processing

  - by Adam Machanic

  Welcome back for the second part of Parallelism Week here at SQL University . Get your pencils ready, and make sure to raise your hand if you have a question. Last time we covered the necessary background material to help you understand how the SQL Server Operating System schedules its many active threads, and the differences between its behavior and that of the Windows operating system's scheduler. We also discussed some of the variations on the theme of parallel processing. Today we'll take a look...(read more)

  Read the article

• SQL SERVER – Fix Error: Microsoft OLE DB Provider for SQL Server error ’80040e07' or Microsoft SQL Native Client error ’80040e07'

  - by pinaldave

  I quite often receive questions where users are looking for solution to following error: Microsoft OLE DB Provider for SQL Server error ’80040e07′ Syntax error converting datetime from character string. OR Microsoft SQL Native Client error ’80040e07′ Syntax error converting datetime from character string. If you have ever faced above error – I have a very simple solution for you. The solution is being very check date which is inserted in the datetime column. This error often comes up when application or user is attempting to enter an incorrect date into the datetime field. Here is one of the examples – one of the reader was using classing ASP Application with OLE DB provider for SQL Server. When he tried to insert following script he faced above mentioned error. INSERT INTO TestTable (ID, MyDate) VALUES (1, '01-Septeber-2013') The reason for the error was simple as he had misspelled September word. Upon correction of the word, he was able to successfully insert the value and error was not there. Incorrect values or the typo’s are not the only reason for this error. There can be issues with cast or convert as well. If you try to attempt following code using SQL Native Client or in your application you will also get similar errors. SELECT CONVERT (datetime, '01-Septeber-2013', 112) The reason here is very simple, any conversion attempt or any other kind of operation on incorrect date/time string can lead to the above error. If you not using embeded dynamic code in your application language but using attempting similar operation on incorrect datetime string you will get following error. Msg 241, Level 16, State 1, Line 2 Conversion failed when converting date and/or time from character string. Remember: Check your values of the string when you are attempting to convert them to string – either there can be incorrect values or they may be incorrectly formatted. Reference: Pinal Dave (http://blog.sqlauthority.com) Filed under: PostADay, SQL, SQL Authority, SQL DateTime, SQL Error Messages, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• Get non-overlapping dates ranges for prices history data

  - by Anonymouse

  Hello, Let's assume that I have the following table: CREATE TABLE [dbo].[PricesHist]( [Product] varchar NOT NULL, [Price] [float] NOT NULL, [StartDate] [datetime] NOT NULL, [EndDate] [datetime] NOT NULL ) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D2C00000000 AS DateTime), CAST(0x00009D2C00000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D2D00000000 AS DateTime), CAST(0x00009D2D00000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 2.5, CAST(0x00009D2E00000000 AS DateTime), CAST(0x00009D2E00000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3000000000 AS DateTime), CAST(0x00009D3000000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3100000000 AS DateTime), CAST(0x00009D3100000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3400000000 AS DateTime), CAST(0x00009D3400000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 2.5, CAST(0x00009D3500000000 AS DateTime), CAST(0x00009D3500000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3600000000 AS DateTime), CAST(0x00009D3600000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3700000000 AS DateTime), CAST(0x00009D3700000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3800000000 AS DateTime), CAST(0x00009D3800000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3A00000000 AS DateTime), CAST(0x00009D3A00000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3B00000000 AS DateTime), CAST(0x00009D3B00000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 2.5, CAST(0x00009D3C00000000 AS DateTime), CAST(0x00009D3C00000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3D00000000 AS DateTime), CAST(0x00009D3D00000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3E00000000 AS DateTime), CAST(0x00009D3E00000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D3F00000000 AS DateTime), CAST(0x00009D3F00000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D4100000000 AS DateTime), CAST(0x00009D4100000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D4200000000 AS DateTime), CAST(0x00009D4200000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 2.5, CAST(0x00009D4300000000 AS DateTime), CAST(0x00009D4300000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D4400000000 AS DateTime), CAST(0x00009D4400000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D4500000000 AS DateTime), CAST(0x00009D4500000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D4600000000 AS DateTime), CAST(0x00009D4600000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 4.9, CAST(0x00009D4800000000 AS DateTime), CAST(0x00009D4800000000 AS DateTime)) INSERT [dbo].[PricesHist] ([Product], [Price], [StartDate], [EndDate]) VALUES (N'Apples', 2.5, CAST(0x00009D4A00000000 AS DateTime), CAST(0x00009D4A00000000 AS DateTime)) As you can see, there are two prices on that month for Apples. 4.90 and 2.50. In order to tidy this table up, I need to get this information as a date range rather than a row per day as it currently is. I can obviously do this with Min and Max aggregates easily but the ranges overlap and other business code expect non-overlapping ranges. I also tried to achieve this with self joins and row_number(), but without much success... Here is what I'm trying to achieve as the output: Product | StartDate | EndDate | Price ------------------------------------------- Apples | 01 Mar 2010 | 02 Mar 2010 | 4.90 Apples | 03 Mar 2010 | 03 Mar 2010 | 2.50 Apples | 05 Mar 2010 | 09 Mar 2010 | 4.90 Apples | 10 Mar 2010 | 10 Mar 2010 | 2.50 Apples | 11 Mar 2010 | 16 Mar 2010 | 4.90 Apples | 17 Mar 2010 | 17 Mar 2010 | 2.50 Apples | 18 Mar 2010 | 23 Mar 2010 | 4.90 Apples | 24 Mar 2010 | 24 Mar 2010 | 2.50 Apples | 25 Mar 2010 | 30 Mar 2010 | 4.90 Apples | 31 Mar 2010 | 31 Mar 2010 | 2.50 What would please be the best approach to get this done? Thanks a lot in advance,

  Read the article

• Complex query making site extremely slow

  - by Basit

  select SQL_CALC_FOUND_ROWS DISTINCT media.*, username from album as album, album_permission as permission, user as user, media as media , word_tag as word_tag, tag as tag where ((media.album_id = album.album_id and album.private = 'yes' and album.album_id = permission.album_id and (permission.email = '' or permission.user_id = '') ) or (media.album_id = album.album_id and album.private = 'no' ) or media.album_id = '0' ) and media.status = '1' and media.user_id = user.user_id and word_tag.media_id = media.media_id and word_tag.tag_id = tag.tag_id and tag.name in ('justin','bieber','malfunction','katherine','heigl','wardrobe','cinetube') and media.media_type = 'video' and media.media_id not in ('YHL6a5z8MV4') group by media.media_id order by RAND() #there is limit too, by 20 rows.. i dont know where to begin explaining about this query, but please forgive me and ask me if you have any question. following is the explanation. SQL_CALC_FOUND_ROWS is calculating how many rows are there and will be using for pagination, so it counts total records, even tho only 20 is showing. DISTINCT will stop the repeated row to display. username is from user table. album, album_permission. its checking if album is private and if it is, then check if user has permission, by user_id. i think rest is easy to understand, but if you need to know more about it, then please ask. im really frustrated by this query and site is very slow or not opening sometimes cause of this query. please help

  Read the article

• SQL SERVER – SSMS Automatically Generates TOP (100) PERCENT in Query Designer

  - by pinaldave

  Earlier this week, I was surfing various SQL forums to see what kind of help developer need in the SQL Server world. One of the question indeed caught my attention. I am here regenerating complete question as well scenario to illustrate the point in a precise manner. Additionally, I have added added second part of the question to give completeness. Question: I am trying to create a view in Query Designer (not in the New Query Window). Every time I am trying to create a view it always adds  TOP (100) PERCENT automatically on the T-SQL script. No matter what I do, it always automatically adds the TOP (100) PERCENT to the script. I have attempted to copy paste from notepad, build a query and a few other things – there is no success. I am really not sure what I am doing wrong with Query Designer. Here is my query script: (I use AdventureWorks as a sample database) SELECT Person.Address.AddressID FROM Person.Address INNER JOIN Person.AddressType ON Person.Address.AddressID = Person.AddressType.AddressTypeID ORDER BY Person.Address.AddressID This script automatically replaces by following query: SELECT TOP (100) PERCENT Person.Address.AddressID FROM Person.Address INNER JOIN Person.AddressType ON Person.Address.AddressID = Person.AddressType.AddressTypeID ORDER BY Person.Address.AddressID However, when I try to do the same from New Query Window it works totally fine. However, when I attempt to create a view of the same query it gives following error. Msg 1033, Level 15, State 1, Procedure myView, Line 6 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified. It is pretty clear to me now that the script which I have written seems to need TOP (100) PERCENT, so Query . Why do I need it? Is there any work around to this issue. I particularly find this question pretty interesting as it really touches the fundamentals of the T-SQL query writing. Please note that the query which is automatically changed is not in New Query Editor but opened from SSMS using following way. Database >> Views >> Right Click >> New View (see the image below) Answer: The answer to the above question can be very long but I will keep it simple and to the point. There are three things to discuss in above script 1) Reason for Error 2) Reason for Auto generates TOP (100) PERCENT and 3) Potential solutions to the above error. Let us quickly see them in detail. 1) Reason for Error The reason for error is already given in the error. ORDER BY is invalid in the views and a few other objects. One has to use TOP or other keywords along with it. The way semantics of the query works where optimizer only follows(honors) the ORDER BY in the same scope or the same SELECT/UPDATE/DELETE statement. There is a possibility that one can order after the scope of the view again the efforts spend to order view will be wasted. The final resultset of the query always follows the final ORDER BY or outer query’s order and due to the same reason optimizer follows the final order of the query and not of the views (as view will be used in another query for further processing e.g. in SELECT statement). Due to same reason ORDER BY is now allowed in the view. For further accuracy and clear guidance I suggest you read this blog post by Query Optimizer Team. They have explained it very clear manner the same subject. 2) Reason for Auto Generated TOP (100) PERCENT One of the most popular workaround to above error is to use TOP (100) PERCENT in the view. Now TOP (100) PERCENT allows user to use ORDER BY in the query and allows user to overcome above error which we discussed. This gives the impression to the user that they have resolved the error and successfully able to use ORDER BY in the View. Well, this is incorrect as well. The way this works is when TOP (100) PERCENT is used the result is not guaranteed as well it is ignored in our the query where the view is used. Here is the blog post on this subject: Interesting Observation – TOP 100 PERCENT and ORDER BY. Now when you create a new view in the SSMS and build a query with ORDER BY to avoid the error automatically it adds the TOP 100 PERCENT. Here is the connect item for the same issue. I am sure there will be more connect items as well but I could not find them. 3) Potential Solutions If you are reading this post from the beginning in that case, it is clear by now that ORDER BY should not be used in the View as it does not serve any purpose unless there is a specific need of it. If you are going to use TOP 100 PERCENT with ORDER BY there is absolutely no need of using ORDER BY rather avoid using it all together. Here is another blog post of mine which describes the same subject ORDER BY Does Not Work – Limitation of the Views Part 1. It is valid to use ORDER BY in a view if there is a clear business need of using TOP with any other percentage lower than 100 (for example TOP 10 PERCENT or TOP 50 PERCENT etc). In most of the cases ORDER BY is not needed in the view and it should be used in the most outer query for present result in desired order. User can remove TOP 100 PERCENT and ORDER BY from the view before using the view in any query or procedure. In the most outer query there should be ORDER BY as per the business need. I think this sums up the concept in a few words. This is a very long topic and not easy to illustrate in one single blog post. I welcome your comments and suggestions. Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: PostADay, SQL, SQL Authority, SQL Query, SQL Server, SQL Server Management Studio, SQL Tips and Tricks, SQL View, T SQL, Technology

  Read the article

• How to optimize this MySQL query

  - by James Simpson

  This query was working fine when the database was small, but now that there are millions of rows in the database, I am realizing I should have looked at optimizing this earlier. It is looking at over 600,000 rows and is Using where; Using temporary; Using filesort (which leads to an execution time of 5-10 seconds). It is using an index on the field 'battle_type.' SELECT username, SUM( outcome ) AS wins, COUNT( * ) - SUM( outcome ) AS losses FROM tblBattleHistory WHERE battle_type = '0' && outcome < '2' GROUP BY username ORDER BY wins DESC , losses ASC , username ASC LIMIT 0 , 50

  Read the article

1 2 3 4 5 6 7 8 9 10 11 12  | Next Page >