Convertir texto a fecha en sql server

Question

Convertir texto a fecha en sql server

rcherod

SSC Eights!

Points: 852
More actions
April 24, 2013 at 12:59 pm

#271557

Estimados, 🙂
quisiera realizar una consulta acerca de como convertir un formato extraño de fecha, tengo un archivo txt que viene con el campo fecha de la siguiente manera :
Nombrefecha_asist
Pedro41388
Ana41389
Juan41390
al subirlo a la tabla de mi base de datos me percate que la fechas habian sido guardadas como texto o general en excel; a un txt delimitado por tabulaciones. Mi pregunta es como podria convertirlo en sql a fecha, de la misma manera que excel convierte a fecha para que la tabla quede asi
NombreFecha Original
Pedro24/04/2013
Ana25/04/2013
Juan26/04/2013
de antemano gracias por sus respuestas o sugerencias.:cool:

Viewing 9 posts - 1 through 8 (of 8 total)

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

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

rcherod (4/24/2013)
Estimados, 🙂
quisiera realizar una consulta acerca de como convertir un formato extraño de fecha, tengo un archivo txt que viene con el campo fecha de la siguiente manera :
Nombrefecha_asist
Pedro41388
Ana41389
Juan41390
al subirlo a la tabla de mi base de datos me percate que la fechas habian sido guardadas como texto o general en excel; a un txt delimitado por tabulaciones. Mi pregunta es como podria convertirlo en sql a fecha, de la misma manera que excel convierte a fecha para que la tabla quede asi
NombreFecha Original
Pedro24/04/2013
Ana25/04/2013
Juan26/04/2013
de antemano gracias por sus respuestas o sugerencias.:cool:

I have no idea what you are saying but hope this helps:

select

Nombre,

dateadd(day, fecha_asist - 2, 0) as FechaOriginal

from

dbo.YourTable;

Lowell SSC Guru Points: 323518 More actions · Answer 2

rcherod (4/24/2013)
Estimados, 🙂
I would like to inquire about how to convert a date format of strange, I have a txt file that comes with the date field as follows:
Name fecha_asist
Pedro 41388
Ana 41389
Juan 41390
to upload it to the table of my database I noticed that the dates had been saved as text or excel general, to a tab-delimited txt. My question is how could I turn it into sql to date, in the same way that makes excel date for the board to be so
Name Date Original
Pedro 24/04/2013
Ana 25/04/2013
Juan 26/04/2013
Thanks in advance for your answers and suggestions.: cool:

it depends on the datatype of the fecha_asist.

que depende del tipo de datos del fecha_asist.

Edit: WRONG CALCULATION:

WITH MySampleData(Nombre,fecha_asist)

AS

(

SELECT 'Pedro',41388 UNION ALL

SELECT 'Ana',41389 UNION ALL

SELECT 'Juan',41390

)

SELECT MySampleData.*,

CONVERT(datetime,fecha_asist) As Converted

FROM MySampleData;

--Integer?

/*

Nombrefecha_asistConverted

Pedro413882013-04-26 00:00:00.000

Ana413892013-04-27 00:00:00.000

Juan413902013-04-28 00:00:00.000

*/

WITH MySampleData(Nombre,fecha_asist)

AS

(

SELECT 'Pedro','41388' UNION ALL

SELECT 'Ana','41389' UNION ALL

SELECT 'Juan','41390'

)

--varchar?

SELECT MySampleData.*,

CONVERT(datetime,CONVERT(int,fecha_asist)) As Converted

FROM MySampleData;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rcherod SSC Eights! Points: 852 More actions · Answer 3

Hi everybody, 🙂

I have a .txt, with datetime as field such as that:

namedate_asist

Pedro41388

Ana41389

Juan41390

When I load txt to my OLEDB I need to convert that field "date_asist" to datetime but I cant in SQL. Anyone knows how can I upload that field in type datetime?

I know can I convert that in Excel, but I need to convert that in SQL, if u know how in SSIS will better.

I put a example how I need to look:

nameDate_Asist

Pedro24/04/2013

Ana25/04/2013

Juan26/04/2013

Thank u very much,

I´ll hope for ur answers.

good bye

Lowell SSC Guru Points: 323518 More actions · Answer 4

Lynn, do you know why my calculation is off by two days? is it because SQL's zero date on 01/01/1900, but Excel thinks zero date is 12/30/1899 ?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

Lowell (4/24/2013)
Lynn, do you know why my calculation is off by two days? is it because SQL's zero date on 01/01/1900, but Excel thinks zero date is 12/30/1899 ?

Not really, I just know that the numeric values for the dates is off by 2. Discovered this while having to work with data from Excel many years ago and it just stuck with me.

rcherod SSC Eights! Points: 852 More actions · Answer 6

Thank u very much!!

Now I have 2 great answers!!

SELECT CONVERT(datetime,CONVERT(int,'41389')-2) As Converted

select dateadd(day,'41389' - 2, 0) as FechaOriginal

Thank u Champion and Thank u Insane

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

rcherod (4/24/2013)
Thank u very much!!
Now I have 2 great answers!!
SELECT CONVERT(datetime,CONVERT(int,'41389')-2) As Converted
select dateadd(day,'41389' - 2, 0) as FechaOriginal
Thank u Champion and Thank u Insane

Don't rely on the implicit conversion in the second select:

select dateadd(day,CONVERT(int,'41389') - 2, 0) as FechaOriginal

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 8

Lowell (4/24/2013)
Lynn, do you know why my calculation is off by two days? is it because SQL's zero date on 01/01/1900, but Excel thinks zero date is 12/30/1899 ?

That's a part of it. The other part (extra day) is that they (MA) never (last time I looked/Office 2007) fixed Excel to correctly calculate leap years. 2/29/1900 is not a valid date because 1900 is not a leap year but Excel thinks it is. Y2K wasn't just about 2 and 4 digit years. It was the first century-year in the modern Gregorian calendar that was actually a leap year because it's not only divisible by 100 (the normal disqualifier for leap years) but it's also divisible by 400 (the extra kick in the rule that a lot of people didn't seem to know about including, apparently, some key people at MS).

My recommendation is to never use date serial numbers to convey dates between systems. Always use ISO dates or spelled out dates like "24 APR 2013'.

--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)