Yahoo Web Search

Search results

1. Top results related to convert date in sql

   • CONVERT Date INT to DATE in SQL

     Top Answer

     Answered Jul 17, 2023 · 5 votes

     (Read below for my answer for if it's an int column)

     Assuming it's a textual string:

     Assuming that datadate is a string (character, text, etc) column and not a date/datetime/datetime2/datetimeoffset column, then use the CONVERT function with style: 23. The 23 value corresponds to ISO 8601 because the values are in yyyy-MM-dd-order, even though they're missing dashes.

     This page has a reference of style numbers: https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15

     SELECT *FROM ( SELECT myTable.* CONVERT( date, datadate, 23 ) AS valueAsDate FROM myTable ) AS qWHERE q.valueAsDate = DATEADD( dd, -1, GETDATE() )-

     The quick-and-dirty way is to convert the int to varchar and then use the same code as above as if it were a textual field - but don't do this because it's slow:

     SELECT *FROM ( SELECT myTable.*, CONVERT( char(8), datadate ) AS valueAsChar, CONVERT( date, CONVERT( char(8), datadate ), 23 ) AS valueAsDate FROM myTable ) AS qWHERE q.valueAsDate = DATEADD( dd, -1, GETDATE() )-

     (I extend my gratitude to @alain for pointing out a rather egregious error in my arithmetic and providing a better solution)

     We'll need to use DATEFROMPARTS and extract each component using Base-10 arithmetic (fun)!

     Supposing we have an integer representing a formatted date (the horror) such as 20200531 then we need to use integer-only arithmetic, such that division truncates rather than rounds - so dividing by 10000, we get 2020, then multiply back to get 20200000:

     20200531 / 10000 => 2020 2020 * 10000 => 2020000020200531 - 20200000 => 531 531 / 100 => 5 5 * 100 => 500 531 - 500 => 31-

     Or:

     datadate / 10000 => yyyy yyyy * 10000 => yyyy0000datadate - yyyy0000 => MMdd MMdd / 100 => MM MM * 100 => MM00 MMdd - MM00 => dd-

     ...which reduces down to:

     yyyy = ( datadate / 10000 ) MM = ( datadate - ( yyyy * 10000 ) ) / 100 dd = ( ( datadate - ( yyyy * 10000 ) ) - ( ( MM ) * 100 ) )-

     ...which can be converted into a T-SQL scalar UDF:

     CREATE FUNCTION dbo.UnhorriblifyDate( @date int )RETURNS date WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT, INLINE = ONASBEGIN- DECLARE @yyyy int = @date / 10000; DECLARE @mm int = ( @date - ( @yyyy * 10000 ) ) / 100; DECLARE @dd int = ( ( @date - ( @yyyy * 10000 ) ) - ( ( @mm ) * 100 ) ); DECLARE @result date = DATEFROMPARTS( @yyyy, @mm, @dd ); RETURN @result;END-

     Or in a query:

     WITH dateParts AS (-SELECT c.datadata, c.yyyy, c.mm, ( ( c.datadate - ( c.yyyy * 10000 ) ) - ( ( c.MM ) * 100 ) ) AS ddFROM ( SELECT b.datadata, b.yyyy, ( b.datadate - ( b.yyyy * 10000 ) ) / 100 AS mm FROM ( SELECT a.datadata, a.datadate / 10000 AS yyyy FROM myTable AS a ) AS b ) AS c)SELECT c.datadata, DATEFROMPARTS( d.yyyy, d.mm, d.dd ) AS valueAsDateFROM dateParts AS d;-

     Check all answers on Stack Overflow

     1/5

   • Convert 'NULL' to Date in SQL

     Top Answer

     Answered Nov 19, 2016 · 1 votes

     nullif([startdate],'NULL') returns [startdate] unless it equals to 'NULL' and then it returns NULL (a real NULL, not the string 'NULL')

     INSERT INTO Destination_Table [new_date] SELECT CONVERT(DATE,nullif([startdate],'NULL'),103) from Source_Table-

     For learning purposes, here are some expressions with the same results:

     nullif(x,y)-

     case when x=y then null else x end-

     case x when y then null else x end-

     Check all answers on Stack Overflow

     2/5

   • Convert julian date to date in sql server

     Top Answer

     Answered Oct 07, 2015 · 3 votes

     You can just cast the INT value to a DATETIME.

     For example:

     SELECT CAST(39596 AS DATETIME); -- Returns 2008-05-30 00:00:00.000-

     Check all answers on Stack Overflow

     3/5

   • convert date to YYMM format in sql for given date

     Top Answer

     Answered Aug 20, 2012 · 4 votes

     Use convert with style 12 and pick the first four characters.

     select convert(char(4), getdate(), 12)-

     Check all answers on Stack Overflow

     4/5

   • convert date to spanish in sql

     Top Answer

     Answered May 23, 2013 · 2 votes

     Maybe is cumbersome, but I don't see how to do it on an easier way.

     First, create a function. This function will make use of system view sys.syslanguages to get the correct name of month in spanish. Parameters are a valid date and language (alias on sys.syslanguage view).

     CREATE FUNCTION [dbo].[fn_GetMonthName] ( @Date DATETIME, @Language NVARCHAR(100))RETURNS NVARCHAR(400)ASBEGIN DECLARE @i INT, @m INT,@mlist NVARCHAR(1000) SET @m = MONTH(@Date) SET @mlist = (SELECT months FROM sys.syslanguages WHERE ALIAS = @language) SET @i = 1 WHILE(@i < @m) BEGIN SET @mlist = REPLACE(@mlist, SUBSTRING(@mlist,1,CHARINDEX(',',@mlist)) ,'') SET @i = @i + 1 END SET @mlist = (CASE CHARINDEX(',',@mlist) WHEN 0 THEN @mlist ELSE SUBSTRING(@mlist,0,CHARINDEX(',',@mlist) ) END ) RETURN @mlistENDGO

     Then call the function anywhere you need to:

     SELECT CONVERT(VARCHAR(20), GETDATE(), 100) AS CurrentDate, dbo.fn_GetMonthName (GETDATE(), 'Spanish') AS [Mes-Month]

     Result:

     CurrentDate Mes-Month May 24 2013 12:02AM Mayo

     Taken from Get Language specific Month Name from SQL

     Check all answers on Stack Overflow

     5/5

   Show moreShow less

2. www.w3schools.com › SQL › func_sqlserver_convertSQL Server CONVERT() Function - W3Schools

   www.w3schools.com › SQL › func_sqlserver_convert

   • Cached

   Definition and Usage. The CONVERT () function converts a value (of any type) into a specified datatype. Tip: Also look at the CAST () function. Syntax. CONVERT ( data_type (length), expression, style) Parameter Values. Technical Details. Works in:

3. learn.microsoft.com › en-us › sqlCAST and CONVERT (Transact-SQL) - SQL Server | Microsoft Learn

   learn.microsoft.com › en-us › sql

   • Cached

   Starting with GETDATE() values, this example displays the current date and time, uses CAST to change the current date and time to a character data type, and then uses CONVERT to display the date and time in the ISO 8601 format.

4. www.sqlshack.com › sql-convert-date-functions-and-formatsSQL Convert Date functions and formats - SQL Shack

   www.sqlshack.com › sql-convert-date-functions-and-formats

   • Cached

   Apr 3, 2019 · We can use the SQL CONVERT () function in SQL Server to format DateTime in various formats. Syntax for the SQ: CONVERT () function is as follows. 1. SELECT CONVERT (data_type(length)),Date, DateFormatCode) Data_Type: We need to define data type along with length. In the date function, we use Varchar (length) data types.

5. www.mssqltips.com › sqlservertip › 1145Date and Time Conversions Using SQL Server

   www.mssqltips.com › sqlservertip › 1145

   • Cached

   Nov 13, 2023 · Learn SQL date format options with the SQL CONVERT function when working with date data types in SQL Server.

6. www.sqlshack.com › sql-convert-dateSQL convert date - SQL Shack

   www.sqlshack.com › sql-convert-date

   • Cached

   Apr 1, 2019 · In this article, we learned how to do a SQL convert date in SQL Server. We learned how to modify the data type in a table, how to use the CAST, CONVERT and FORMAT functions. We also learned about the different types of SQL data types.

7. www.sqlservertutorial.net › sql-server-system-functions › sql-server-convert-functionSQL Server CONVERT Function Explained By Practical Examples

   www.sqlservertutorial.net › sql-server-system-functions › sql-server-convert-function

   • Cached

   Mar 14, 2019 · This example uses the CONVERT() function to convert the current date and time to a string with a specific style: SELECT CONVERT ( VARCHAR , GETDATE (), 13 ) result ; Code language: SQL (Structured Query Language) ( sql )

8. www.mssqltips.com › sqlservertip › 6971Examples for SQL CAST and SQL CONVERT Functions - SQL Server Tips

   www.mssqltips.com › sqlservertip › 6971

   • Cached

   Sep 16, 2021 · If you want to convert a "date-like" number to a date, such as the integer 20210805, you'll need to convert it to a string first: SELECT CONVERT(DATE,CONVERT(CHAR(8),20210805)); More info can be found in the tip SQL Server function to convert integer date to datetime format , or the tip SQL Convert Date to YYYYMMDD for the other way around.

9. database.guide › how-to-convert-dates-in-sql-serverHow to Convert Dates in SQL Server - Database.Guide

   database.guide › how-to-convert-dates-in-sql-server

   • Cached

   Jan 18, 2023 · See 6 Ways to Convert a String to a Date/Time Value in SQL Server for more examples and other options. Convert a Time to a Date. We can convert time values to date data types even though time values don’t contain a date portion.

10. www.mssqltips.com › sqlservertip › 8004SQL Convert Examples for Dates, Integers, Strings and more

    www.mssqltips.com › sqlservertip › 8004

    • Cached

    May 28, 2024 · We'll look at several examples of using the SQL CONVERT function to convert and optionally format date, datetime, string, and integer data types. Here are some reasons you might use the CONVERT function:

11. www.sqltutorial.net › convertCONVERT – SQL Tutorial

    www.sqltutorial.net › convert

    • Cached

    The basic syntax of the CONVERT function is as follows: CONVERT(data_type, expression, style) data_type: The target data type to which you want to convert the expression. expression: The expression or value to be converted. style: (Optional) This parameter is used when converting from date and time data types.