Reset varchar length to 19 from 4000 in a concatenated date

Question

Reset varchar length to 19 from 4000 in a concatenated date

Jocar26

SSChasing Mays

Points: 631
More actions
October 10, 2022 at 9:36 pm

#4097682
I import a table from a comma delimited file that has a separate date column and a separate time column (among other columns.) I used to import the data into an Excel spreadsheet and manipulate it there and append the data to an existing table, but now I want to do it in Sql Server . I want to add this data to another table that I have in which the date and time is one column (formatted varchar(19)). The date field is formatted (date,null) and the time field is (Time(7),null) in the delimited file. I wrote the code below that matches the format of the datetime column in table I am copying to , but the datetime column in the delimited file saves as varchar(4000). When I do a union to combine the two tables into one table, the resulting datetime column assumes the formatting of the larger column.
I really googled how to concatenate date and time with the formatting I need (space between date and time and a space between time and am/pm and below seemed to be the way I wanted it. I would really like the datetime column to be varchar(19). Is there a way that I can change the varchar(4000) to Varchar(19)? The data has 10,000 rows with 15 columns. Am I too worried about using too much space with a column length of 4000?
Thanks in advance
Carroll
```
Select 
date
,time
,Concat(Convert(varchar(10),Date,23) , ' ',REPLACE(REPLACE(RIGHT('0'+LTRIM(RIGHT(CONVERT(varchar(8),time,100),7)),7),'AM',' AM'),'PM',' PM')) as DateTime
from DateTemp

Output
Year  Week Status DateTime
2002   01  1-R    2002-09-05 08:38 PM
2002   01  1-R    2002-09-08 01:04 PM
2002   01  1-R    2002-09-08 01:04 PM
2002   01  1-R    2002-09-08 01:04 PM
2002   01  1-R    2002-09-08 01:04 PM
```
- This topic was modified 2 years, 1 month ago by Jocar26.
- This topic was modified 2 years, 1 month ago by Jocar26. Reason: I formatted the output for readability

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

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

ScottPletcher SSC Guru Points: 101095 More actions · Answer 1

CAST the column formula to the data type you want it to be, e.g.:

,CAST(Concat(Convert(varchar(10),Date,23) , ' ',REPLACE(REPLACE(RIGHT('0'+LTRIM(RIGHT(CONVERT(varchar(8),time,100),7)),7),'AM',' AM'),'PM',' PM')) AS datetime) as DateTime

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

ratbak Ten Centuries Points: 1358 More actions · Answer 2

I strongly encourage you to store date/time data as datetime or datetime2 datatype, not varchar. This will enable use datetime math and/or functions, efficiently compare datetime values, & to avoid conversions. Do the formatting in the client that consumes the data, or if you must, when querying the data.

It is fine to import it into a staging table as varchar, especially if format of source data is non-standard.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 3

@Carroll,

You said...

I import a table from a comma delimited file...

I'm curious as to what you're using to do the import of the file.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jocar26 SSChasing Mays Points: 631 More actions · Answer 4

I am using right click on database, tasks, import flat file wizard. Since I wrote the question, I have been looking for alternatives and now am using import databases. I found that I can change the format of the columns before I import the data and get exactly what I need. Once I get all my ducks in a row, I will be making a full explanation of what I did to correct this. Now I am trying to save the formatting in the import/export wizard so I can call this at any time.

Thanks for your question

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 5

Consider, perhaps, the utility of thing like BULK INSERT and BCP and even the ACE Drivers (which actually pale in comparison to the old JET drivers).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

your varchar(4000) consumes as much space as your varchar(19) !

That's the whole point of data type varchar !

Do NOT store date/time/datetime date in (var)char columns !

Always use the correct data type for your columns !

Why? Eventually someone will inject '24/10/2022 12:00:00' into a row. (char formating can be client setting)

How will this work out with your selecting/reporting based on the given datetime information?

Msg 241, Level 16, State 1, Line 83

Conversion failed when converting date and/or time from character string.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me