Insert statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Insert statement

Author

Topic

Vack
Aged Yak Warrior

530 Posts

Posted - 2014-07-11 : 14:11:26

I have a datetime field I'm inserting records into from a field that is int.

So some of the values have 20141214 and some have zero.

If the field is zero how do I insert NULL?

I'm using this to convert a non zero value to a date.

convert(datetime,cast(picked_dt, as char(8)))

insert into oeordlin(
picked_dt
select
?????
from oeord2

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-11 : 14:13:07

Change your convert to

CASE WHEN picked_dt = 0 THEN NULL ELSE
convert(datetime,cast(picked_dt, as char(8)))
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-11 : 14:14:12

select case when picked_dt <> 0 then convert(datetime,cast(picked_dt, as char(8))) else null end
from oeord2

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-11 : 14:14:36

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-07-11 : 14:18:31

Using NULLIF instead of a CASE expression:

SELECT CONVERT(DATETIME, CAST(NULLIF(picked_dt, 0) AS CHAR(8)), 112)

Vack
Aged Yak Warrior

530 Posts

Posted - 2014-07-11 : 14:22:11

I'm getting incorrect syntax near ','

Here is the entire piece of Code.

INSERT INTO OEORDLIN_SQL
(
ORD_TYPE,
ORD_NO,
LINE_SEQ_NO,
ITEM_NO,
LOC,
PICK_SEQ,
CUS_ITEM_NO,
ITEM_DESC_1,
ITEM_DESC_2,
QTY_ORDERED,
QTY_TO_SHIP,
UNIT_PRICE,
DISCOUNT_PCT,
REQUEST_DT,
QTY_BKORD,
QTY_RETURN_TO_STK,
BKORD_FG,
UOM,
UOM_RATIO,
UNIT_COST,
UNIT_WEIGHT,
COMM_CALC_TYPE,
COMM_PCT_OR_AMT,
PROMISE_DT,
TAX_FG,
STOCKED_FG,
CONTROLLED_FG,
SELECT_CD,
TOT_QTY_ORDERED,
TOT_QTY_SHIPPED,
TAX_FG_1,
TAX_FG_2,
TAX_FG_3,
ORIG_PRICE,
COPY_TO_BM_FG,
EXPLODE_KIT,
MFG_ORD_NO,
ALLOCATE_DT,
LAST_POST_DT,
POST_TO_INV_QTY,
POSTED_TO_INV,
TOT_QTY_POSTED,
QTY_ALLOCATED,
COMPONENTS_ALLOC,
BIN_FG,
COST_METH,
SER_LOT_CD,
MULT_FTR_FG,
LINE_TYPE,
PROD_CAT,
END_ITEM_CD,
REASON_CD,
FEATURE_RETURN,
REC_INSPECTION,
SHIP_FROM_STK,
MULT_RELEASE,
REQ_SHIP_DT,
QTY_FROM_STK,
USER_DEF_FLD_1,
USER_DEF_FLD_2,
USER_DEF_FLD_3,
USER_DEF_FLD_4,
USER_DEF_FLD_5,
PICKED_DT,
SHIPPED_DT,
BILLED_DT,
UPDATE_FG,
PRC_CD_ORIG_PRICE,
TAX_SCHED,
CUS_NO,
TAX_AMT,
QTY_BKORD_FG,
LINE_NO,
MFG_METHOD,
FORCED_DEMAND,
CONF_PICK_DT,
ITEM_RELEASE_NO,
BIN_SER_LOT_COMP,
OFFSET_USED_FG,
ECS_SPACE,
SFC_ORDER_STATUS,
TOTAL_COST,
PO_ORD_NO,
RMA_SEQ,
VENDOR_NO,
FILLER_0004
)
SELECT
ORD_TYPE,
right(' '+cast(convert(int,ord_no)as VARCHAR(8)),8),
LINE_SEQ_NO,
ITEM_NO + ITEM_FILLER,
LOC,
PICK_SEQ,
CUS_ITEM_NO,
ITEM_DESC_1,
ITEM_DESC_2,
QTY_ORDERED,
QTY_TO_SHIP,
UNIT_PRICE,
DISCOUNT_PCT,
case when request_dt = 0 then null else convert(datetime,cast(request_DT as CHAR(8))),
QTY_BKORD,
QTY_RETURN_TO_STK,
BKORD_FG,
UOM,
UOM_RATIO,
UNIT_COST,
UNIT_WEIGHT,
COMM_CALC_TYPE,
COMM_PCT_OR_AMT,
case when promise_dt = 0 then null else convert(datetime,cast(promise_DT as CHAR(8))),
TAX_FG,
STOCKED_FG,
CONTROLLED_FG,
SELECT_CD,
TOT_QTY_ORDERED,
TOT_QTY_SHIPPED,
TAX_FG_1,
TAX_FG_2,
TAX_FG_3,
ORIG_PRICE,
COPY_TO_BM_FG,
EXPLODE_KIT,
0,
0,
0,
POST_TO_INV_QTY,
POSTED_TO_INV,
TOT_QTY_POSTED,
QTY_ALLOCATED,
COMPONENTS_ALLOC,
BIN_FG,
COST_METH,
SER_LOT_CD,
MULT_FTR_FG,
LINE_TYPE,
PROD_CAT,
END_ITEM_CD,
REASON_CD,
FEATURE_RETURN,
REC_INSPECTION,
SHIP_FROM_STK,
MULT_RELEASE,
case when req_ship_dt = 0 then null else convert(datetime,cast(req_ship_dt as CHAR(8))),
QTY_FROM_STK,
USER_DEF_FLD_1,
USER_DEF_FLD_2,
USER_DEF_FLD_3,
USER_DEF_FLD_4,
USER_DEF_FLD_5,
case when picked_dt = 0 then NULL else convert(datetime,cast(picked_dt as CHAR(8))),
case when shipped_dt = 0 then NULL else convert(datetime,cast(shipped_dt as CHAR(8))),
case when billed_dt = 0 then NULL else convert(datetime,cast(billed_dt as CHAR(8))),
UPDATE_FG,
PRC_CD_ORIG_PRICE,
TAX_SCHED,
CUS_NO,
TAX_AMT,
QTY_BKORD_FG,
LINE_NO,
MFG_METHOD,
FORCED_DEMAND,
convert(datetime,cast(conf_pick_dt as CHAR(8))),
0,
BIN_SER_LOT_COMP,
OFFSET_USED_FG,
ECS_SPACE,
SFC_ORDER_STATUS,
TOTAL_COST,
0,
ISNULL(RMA_LINE_SEQ_NO,0),
VEND_NO,
FILLER_0004
FROM [dataes].dbo.OEORDLIN_SQL

Vack
Aged Yak Warrior

530 Posts

Posted - 2014-07-11 : 14:23:46

Never Mind.

Forgot the END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources