Tag: datetime

SQL Server 2014 Trouble with Convert Function

For some reason the following queries return the same result, even though they shouldn’t SELECT CONVERT(DATE, GETDATE(),101) SELECT CONVERT(DATE, GETDATE(),102) SELECT CONVERT(DATE, GETDATE(),103) SELECT CONVERT(DATE, GETDATE(),104) SELECT CONVERT(DATE, GETDATE(),105)– Ad Infinitum (Tested up to 140) Result 2014-10-07 Just in case today’s date is 7-Oct-2014 According to http://msdn.microsoft.com/es-es/library/ms187928.aspx there should only be 131 styles, IS the […]

Filling down missing (time-period) rows in a SQL query result-set

Update: 8/15/2014: See final working solution at the end, based on LMU92’s suggestions/sample I have a query that reads a financial transactions table. The table is a roll-up of detail transactions, is regenerated nightly, and is used for read/SELECT only. Platform is SQL Server 2012. This sampling is result of of the primary query, which […]

How to convert Date and Time column to just time as a decimal

Does anybody know how I can convert a date and time column to a decimal column based on the time only? For example: Select StaffNum, Name, ContractHours From StaffContract Where StaffNum = 00123 Returns **StaffNum**——–**Name**—————-**ContractHours** 00123———–Shaw, Jason——–2012-10-01 07:30:000 Instead of the ContractHours column showing ‘2012-10-01 **07:30:000**’ I need it to show the time section only […]

How to standardize datetime format for all regions?

Within my program i insert into the sql server the current time into a sql DateTime column. Because the program is used by users around the world , i found out that some american users are receiving an error (which doesn’t cause the program to crash) and i believe this is happening because of different […]

How to insert DBnull to nullable DateTime field asp.net c#

I need to insert null into a class field datetime? this is the class: public class TimeData{ public int TD_ID { get; set; } public int UD_ID { get; set; } public DateTime? TD_start { get; set; } public DateTime? TD_end { get; set; } public DateTime? timestemp { get; set; } public void Insert(int […]

tsql datetime vs int query performance

Would it make any sense to store a datetime as an int or bigint for performance purposes. Wondering if indexing/querying the integer column would yield better performance than a datetime column. If there is a performance difference does anyone know if it is substantial, or relatively negligible.

Format date to include day of week

I have a SQL Server 2012 query that converts date to VARCHAR SELECT CONVERT(VARCHAR(12), dbo.Download.Date_of_Download, 107) as Date_to_Display, dbo.Download.Number_of_Computers FROM dbo.Download ORDER BY dbo.Download.Date_of_Download DESC Below are results Date_to_Display Number_of_Computers ———————————– Aug 14, 2014 240 Aug 13, 2014 519 Aug 12, 2014 622 Aug 11, 2014 2132 Aug 10, 2014 1255 Aug 09, 2014 3240 […]

SQL Server convert string to datetime fails

I have data like this : +—–+———+———-+ | id | name | expdate | +—–+———+———-+ | 1 | chookies| 02/2015 | | 2 | snack | 03/2015 | | 3 | snack1 | 04/2015 | +—–+———+———-+ Datatype of expdate is varchar(7) and I want to known name of cookies where expired date from now to […]

SQL Server 2008 – split time intervals given by StartTime, EndTime at selected points

In SQL Server 2008, I have a table containing time intervals in the form of columns StartTime, EndTime. The table also contains some other identifiers. The first 3 rows of this table look like this: create table #ExampleTable (ID int, PRDATE date, RID int, StartTime DateTime, EndTime DateTime); INSERT INTO #ExampleTable(ID, PRDATE, RID, StartTime, EndTime) […]

select from table sql ce server between date returning 0

I am developing a windows form application in C#. I have a query where i want to select from date to date. the result is always 0. My DB is a .sdf file, and in my table I have a datetime field to store the order date as DateTime.Now, and my PC dateformat is 10.12.2014, […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.