dateadd(day, max(field1) % 1000, dateadd(year, floor(max(field1)/1000), '31-dec-1899')) --must be formatted as text, MM/DD/YYYY

Question

dateadd(day, max(field1) % 1000, dateadd(year, floor(max(field1)/1000), '31-dec-1899')) --must be formatted as text, MM/DD/YYYY

Krasavita

SSC-Insane

Points: 23138
More actions
March 12, 2018 at 1:46 pm

#327806

Hello can someone help me to format this code as text ?
Thank you so muchT

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

Krasavita - Monday, March 12, 2018 1:46 PM
Hello can someone help me to format this code as text ?
Thank you so muchT

Exactly what do you mean by "format this code as text" ? You provided code only in the subject line, which suggests that this might be a test question or homework. As the result of the following query was just a date that was as many years forward of 18991231 as the max value of the column named field1 divided by 1,000 using integer math, I fail to even see why the additional DATEADD was even necessary:SELECT DATEADD(day, MAX(field1) % 1000, DATEADD(year, FLOOR(MAX(field1)/1000), '31-dec-1899')) AS THE_RESULT FROM ( VALUES (90000), (2000), (3000), (400000) ) AS X(field1);

SELECT DATEADD(year, MAX(field1)/1000, '31-dec-1899') AS THE_RESULT FROM ( VALUES (90000), (2000), (3000), (400000) ) AS X(field1);

Both queries produce the same results, but again, what format are you looking for? and for what thing, exactly? The query result? The forum formatting of the query into its own text box?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Krasavita SSC-Insane Points: 23138 More actions · Answer 2

Hello I have this code
dateadd(day,max(fad)/1000, dateadd(year,floor(max(fad)/1000, '21-mar-1899')) as [DateP]
need to convert this to text format as dd/mm/yyyy, now I get this value:2000-11-28 00:00:00.000

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Krasavita - Tuesday, March 13, 2018 9:23 AM
Hello I have this code
dateadd(day,max(fad)/1000, dateadd(year,floor(max(fad)/1000, '21-mar-1899')) as [DateP]
need to convert this to text format as dd/mm/yyyy, now I get this value:2000-11-28 00:00:00.000

Take a look at the CONVERT function in the SQL Server documentation.

Krasavita SSC-Insane Points: 23138 More actions · Answer 4

Krasavita

SSC-Insane

Points: 23138

March 13, 2018 at 10:20 am

#1982973

I am not understanding

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Okay, read: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

SELECT

DatetimeFromINT,

VarcharFromDatetimeFromINT = CONVERT(VARCHAR,DatetimeFromINT,1)

FROM (

SELECT

DatetimeFromINT = dateadd(day, max(field1) % 1000, dateadd(year, floor(max(field1)/1000), '31-dec-1899'))

FROM (SELECT field1 = CAST(118072 AS INT)) d

) e

-- The third parameter in this expression

-- CONVERT(VARCHAR, DatetimeFromINT, 1)

-- is the "style".

-- Read the document kindly linked by Lynn,

-- then choose the correct style for your requirement.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden