converting date

Question

Post reply

converting date

juliava

SSC-Addicted

Points: 444
More actions
December 7, 2023 at 2:55 pm

#4341602

cast(DATEADD(DAY, case when j1 is null then 0 else j1 end, j_date) as date) as 'DateEnd',
I get results as 12/1/2023 12:00:00 AM when data gets imported to my txt file, what can be done to remove time, I need result: 12/1/2023
Thank you

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

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

DesNorton SSC-Insane Points: 24386 More actions · Answer 1

Take a look at the CONVERT function

How to post data/code on a forum to get the best help.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

Added local variables to make the code work in isolation. Your best bet is to output as a string:

DECLARE @J1 INT = 1;
DECLARE @JDate DATETIME = GETDATE ();

SELECT DateEnd = CONVERT (CHAR(10)
                         ,DATEADD (   DAY
                                     ,CASE
                                          WHEN @J1 IS NULL THEN
                                              0
                                          ELSE
                                              @J1
                                      END
                                     ,@JDate
                                  )
                         ,110
                         );

--Simpler version
SELECT DateEnd = CONVERT (CHAR(10), DATEADD (DAY, ISNULL (@J1, 0), @JDate), 110);

This reply was modified 11 months, 2 weeks ago by Phil Parkin.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

juliava SSC-Addicted Points: 444 More actions · Answer 3

Thank you

@JDate is getdate, i dont need that I have j_date that contains data, so pretty much this field j_date need format as mm/dd/yyyy

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

juliava wrote:

Thank you
@JDate is getdate, i dont need that I have j_date that contains data, so pretty much this field j_date need format as mm/dd/yyyy

I get that. Just substitute your columns into the code I provided.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

juliava SSC-Addicted Points: 444 More actions · Answer 5

Thank you I get 11-12-2023 format, I need 11/12/2023

frederico_fonseca SSCoach Points: 16154 More actions · Answer 6

juliava wrote:

Thank you I get 11-12-2023 format, I need 11/12/2023

look at the convert link given about and use the format you need.

and why do you need that particular format on a CSV file? if it is to load to another system there are other formats that are more stable than that one (e.g. how do you know it is mm/dd/yyyy or dd/mm/yyyy if you have a date of 11/12/2023 ?)