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: 18842 More actions · Answer 1

Ken McKelvey

SSCoach

Points: 18842

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: 246959 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.)

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

Jeff Moden SSC Guru Points: 1004318 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: 23786 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).