Tag: datetime

Convert Varchar Column to Datetime format – SQL Server

I have a table of data imported from CSV as follows: FirstTimeTaken LatestTimeTaken Market Outcome Odds NumberOfBets VolumeMatched InPlay 03/08/2013 15:30:14 03/08/2013 15:32:28 Over/Under 3.5 Goals Over 3.5 Goals 5 10 118 1 03/08/2013 14:26:40 03/08/2013 14:29:43 Correct Score 0 – 0 7 12 279 1 03/08/2013 15:15:34 03/08/2013 15:27:39 Match Odds Barnsley 110 7 […]

SQL Server Convert ISO 8601 not working as documented

Per MSDN convert should properly parse ISO 8601 dates with timezone using 127 as the style parameter. The optional time zone indicator, Z, is used to make it easier to map XML datetime values that have time zone information to SQL Server datetime values that have no time zone. Z is the indicator for time […]

Convert varchar dd/mm/yyyy to dd/mm/yyyy datetime

I’m trying to convert a date in a varchar column in the dd/mm/yyyy format into the datetime dd/mm/yyyy format, so then I can run date range queries on the data. So far I have the following which is not working CONVERT(varchar, CAST(date_started AS datetime), 103) I have also tried convert(date_started as datetime, 103) I need […]

SQL Server: Why are dates in ISO-8601 format language dependent?

I need some help understanding date format handling in SQL Server. If you try the following, it will return a correct result: SET LANGUAGE English SELECT CAST(‘2013-08-15′ AS DATETIME) — 2013-08-15 00:00:00.000 This, however, will result in a conversion error because apparently SQL Server interprets ‘8’ as the day and ’15’ as the month: SET […]

DateTime.MinValue vs new DateTime() in C#

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();

DateTime convert in SQL

I have DateTime saved as Integer in row of table in format 20111111. I want to display it by select to : 2011-11-11. Please give me some as simpliest solution. The Datetime is in table and looks like: Table_name 20111212 20111113 20111212 and i want to display it like Table_name 2011-12-12 2011-11-13 2011-12-12 MS SQL […]

Change NULL values in Datetime format to empty string

I have a table which contains ‘NULL’ values which are of type ‘Datetime’. Now i have to convert those into empty string but when when i use convert function ISNULL( [Accrued Out of Default] ,” ) here accrued into default is of datetime type, what it does it changes null to ‘1900-01-01 00:00:00.000’ instead of […]

Subtract minute from DateTime in SQL Server 2005

Suppose I have a datetime field whose value is 2000-01-01 08:30:00 and a duration field whose value is say 00:15 (meaning 15 minutes) If I subtract these two, I should get 2000-01-01 08:15:00 Also if I want to subtract 1:15 (means 1 hour 15 minutes), the output should be 2000-01-01 07:15:00 I am trying SELECT […]

Validate DateTime String in SQL Server 2005

I require a SQL script to validate a VARCHAR field in a table in a SQL Server 2005 database that contains DateTime values, in the format DD/MM/YYYY, or NULL values. I would like to identify all invalid dates. Can anyone suggest a method? UPDATE The answer has to make use of T-SQL; for performance reasons, […]

convert decimal to time 6.80 =7.20 hrs in SQL

This question already has an answer here: Convert decimal time to hours and minutes 4 answers

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