Tag: datetime

SQL Setting Week Numbers For Months From Monday to Sunday

I am trying to set the week number for months in a table. The logic is the first week starts on the first day of the month until the first Sunday and the rest of the weeks start on every Monday following that. I am having trouble implementing this though. Any Ideas? My code looks […]

Stored procedure changes the datetime format

I have a stored procedure that receives some parameters and then returns results. The stored procedure receives a DateTime parameter with the following format ‘2012.01.06’. Then I simply want to return this DateTime parameter. It works fine, but the returned DateTime has this format Jan 6 2012 12:00AM. I want to change it so that […]

SQL – How to ignore seconds and round down minutes in DateTime data type

At work we did a project that required a team to count students 8 times a day over 5 days at specific time periods. They are, as follows :- 09:00, 10:00, 11:00, 13:15, 14:15, 14:50, 15:50, 16:20. Now, the data collected was put directly into a database via a web app. The problem is that […]

T-SQL group by date instead of datetime

select [DateTime (UTC)], sum([messages sent external]) as ‘messages sent external’, sum([messages sent internal]) as ‘messages sent internal’, sum([messages received external]) as ‘messages received external’, sum([messages received internal]) as ‘messages received internal’, max([Message Latency Internal High]) as ‘Message Latency Internal High’, max([Message Latency Internal Avg]) as ‘Message Latency Internal Avg’ from dbo.Monthly_MailFlowStats_2014_03 group by [DateTime (UTC)] […]

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.

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