T-sql 2012 problem with format on an insert statement

Transact-SQL
1

In tsql-2012, I have the sql listed below where I am having a problem with the data placed in the column called 'value'
that is declared as varchar(256) where the value can be null. The 'value' column is not a key field.
Here is the sql:
INSERT INTO test.dbo.Cus (pID, attributeID, value, [date], districtID)
SELECT Participation.pID
, 3668 as attributeID -- SBHCStartDateSB
, cast(Participation.eligibilityStartdate as smalldatetime) as value
,getdate() as [date]
, NULL as districtID
FROM Participation
The data looks like '2017-08-18 00:00:00' for the select column above cast(Participation.eligibilityStartdate as smalldatetime) as value.
However when the data is actually inserted into the 'test.dbo.Cus' tabble, the value is changed to 'Aug 18 2017'.
Thus can you tell me what I can do with the insert statement so that the 'value' column will look like '2017-08-18 00:00:00' ?

2

try this see if it works:

,Convert(varchar(12), cast(Participation.eligibilityStartdate as date),120)

3

This is returning a smalldatetime value - which is then implicitly converted to a varchar(256) in the table. When you convert from a datetime to a string without defining the format for that date string - the default value will be used. In this case - it is 'MMM dd YYYY'.

Either change the 'value' column to be a smalldatetime, datetime, datetime2 or date data type, or convert the value to an appropriate string representation. Please be aware that if you store a date string you will have issues using that value later.

If you must have it as a string - then CONVERT(char(19), CAST(Participation.EligiilityStartDate AS smalldatetime), 120)

With that said - I would think an eligibility start date would not have any time portion. If so - then use DATE for the data type instead of smalldatetime and you can just use: CAST(Participation.EligibilityStartDate AS date) which will be converted to YYYY-MM-DD date format.

1 Like
4

I have not see in dB that date changes from YYYY-MM-DD ( SQL format) to Aug 18 2017? I think MS SQL dB will automatically change the format even if its in Aug 18 2017.
Pasi

5

If you try to convert a datetime/smalldatetime data type to a string - e.g. cast(datecolumn as varchar(12)) - the system will use a default format. To insure that a datetime is converted to a correct string representation - you need to use CONVERT with the style, or change the data type to date.

1 Like