Converting date
Hi,
How can convert date to yyyy-mm-dd, i tried some options in converter and nothing:
The following query works but i think the format is mm-dd-yyyy
select cod,ISNULL(datep,CONVERT(d
this code not work get error when converting, changing 03/09 to 13/09
select cod,ISNULL(datep,CONVERT(d
best regards
How can convert date to yyyy-mm-dd, i tried some options in converter and nothing:
The following query works but i think the format is mm-dd-yyyy
select cod,ISNULL(datep,CONVERT(d
this code not work get error when converting, changing 03/09 to 13/09
select cod,ISNULL(datep,CONVERT(d
best regards
For old times / pre-Y2K's programming sake ... (yes the problem was actually real; I can tell you war stories):
A mathematical way is:
1. Convert a 5 or 6 digit date (but the day must use the leading zero) by taking it times 10000.01, removing the decimal and concatenating the integer to 6 digits.
Example: 10299 * 10000.01 = 102990102.99 and following the concatenation rules ... 990102
2. Convert a 7 or 8 digit date (but the day must use the leading zero and the year must be four digits) by taking it times 10000.0001, removing the decimal and concatenating the integer to 8 digits.
Example: 1021999 * 10000.0001 = 10219990102.1999 and following the concatenation rules ... 19990102
I submitted #2 (after learning of #1 from an older programmer) to an AS/400 magazine and they published it in their "Letters to the Editor" section and titled it "Too Tricky?"
A mathematical way is:
1. Convert a 5 or 6 digit date (but the day must use the leading zero) by taking it times 10000.01, removing the decimal and concatenating the integer to 6 digits.
Example: 10299 * 10000.01 = 102990102.99 and following the concatenation rules ... 990102
2. Convert a 7 or 8 digit date (but the day must use the leading zero and the year must be four digits) by taking it times 10000.0001, removing the decimal and concatenating the integer to 8 digits.
Example: 1021999 * 10000.0001 = 10219990102.1999 and following the concatenation rules ... 19990102
I submitted #2 (after learning of #1 from an older programmer) to an AS/400 magazine and they published it in their "Letters to the Editor" section and titled it "Too Tricky?"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You want to format the final result, but the code is only formatting a null result. That is, you need to:
1) convert after the ISNULL replacement, not during it
2) use varchar as the result type, not date, because date by default will convert to mm-dd-yyyy (as you've already seen).
select cod, CONVERT(varchar(10), ISNULL(datep,'03/09/2018 18:06:53'), 120) as datep
from Refmb
1) convert after the ISNULL replacement, not during it
2) use varchar as the result type, not date, because date by default will convert to mm-dd-yyyy (as you've already seen).
select cod, CONVERT(varchar(10), ISNULL(datep,'03/09/2018 18:06:53'), 120) as datep
from Refmb
SELECT FORMAT(GetDate(), 'yyyy-MM-dd')