Select Into as Number and Date rather than Text

Question

Post reply

Select Into as Number and Date rather than Text

craig.jenkins

SSCommitted

Points: 1643
More actions
November 14, 2018 at 3:48 am

#378628

Guys, how do i ensure the below date columns 'Updated', 'ContDate', 'Complete', 'FollDate' insert as dates not text and that Regno inserts as a number not text
Many thanks
IF OBJECT_ID('tempdb..#Log') IS NOT NULL DROP TABLE #Log
SELECT * INTO #Log FROM (VALUES
( 'BAA', 'SE1', '1', 'Mr Jones 1', 'NULL', '07-11-18', 'NULL', 'NULL', '0', 'RT1', '07-11-18', '1', 'NULL'),
( 'BAA', 'SE1', '2', 'Mr Jones 2', '27-06-18', '06-06-18', 'CUA', '01-01-00', '0', '1', '30-06-18', '2', '4021'),
( 'BAA', 'SE1', '3', 'Mr Jones 3', 'NULL', '31-10-18', 'NULL', 'NULL', '0', 'RT1', '31-10-18', '1', 'NULL'),
( 'BAA', 'MO1', '4', 'Mr Jones 4', 'NULL', '31-10-18', 'NULL', 'NULL', '0', 'RT1', '31-10-18', '1', 'NULL'),
( 'BAA', 'GMO', '5', 'Mr Jones 5', 'NULL', '05-11-18', 'NULL', '01-01-00', '0', 'MOC', '05-11-18', '1', 'NULL'),
( 'BAA', 'SE1', '6', 'Mr Jones 6', 'NULL', '07-11-18', 'NULL', 'NULL', '0', 'RT1', '07-11-18', '1', 'NULL')
) d
( Dealer, Contcode, Salute, Regno, Updated, ContDate, Completecode, Complete, InUse, FollCode, FollDate, Grop,OpNum)
select * from #Log

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

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

Ken McKelvey SSCoach Points: 18768 More actions · Answer 1

Ken McKelvey

SSCoach

Points: 18768

November 14, 2018 at 4:35 am

#2013408

SELECT '07-11-18', CONVERT(date, '07-11-18', 5)

craig.jenkins SSCommitted Points: 1643 More actions · Answer 2

Thanks Ken, how is that written into the above? Sorry, still learning. Many thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

Yes, or create the temp table before you start, instead of doing a SELECT INTO. I always think that's safer.

John

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

An example. Replace this
'07-11-18'
with this
CAST('20181107' as DATE)
(Using YYYYMMDD as your date format removes any D/M/Y M/D/Y ambiguity and is an ISO standard format.)

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

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

In other words, replace the * of your SELECT INTO with the necessary formulas and column name aliases.

Also, if you need for certain columns to be NOT NULL as a result of your SELECT INTO, you can wrap the formulas for those columns in a fairly nonsensical ISNULL() function and the columns will have the constraint of being NOT NULL.

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

craig.jenkins SSCommitted Points: 1643 More actions · Answer 6

craig.jenkins

SSCommitted

Points: 1643

November 14, 2018 at 6:03 am

#2013426

Thanks all much appreciated

Jason A. Long SSC-Insane Points: 23780 More actions · Answer 7

In addition to what others have said, if you want the columns marked as 'NULL' to actually have null values rather than text columns with the word "NULL" you'll need to change from 'NULL' to convert(datatype, NULL).